You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@tooling.apache.org by sb...@apache.org on 2026/08/24 18:16:58 UTC

(tooling-trusted-releases) branch sbp updated: Append every OpenPGP certificate change to the certificate log

This is an automated email from the ASF dual-hosted git repository.

sbp pushed a commit to branch sbp
in repository https://gitbox.apache.org/repos/asf/tooling-trusted-releases.git


The following commit(s) were added to refs/heads/sbp by this push:
     new 7a9003bb Append every OpenPGP certificate change to the certificate log
7a9003bb is described below

commit 7a9003bb5c38cc56d4f68996c0c8f8d22490017d
Author: Sean B. Palmer <se...@miscoranda.com>
AuthorDate: Mon Aug 24 19:06:35 2026 +0100

    Append every OpenPGP certificate change to the certificate log
---
 atr/admin/__init__.py          |   4 +-
 atr/api/__init__.py            |   8 +-
 atr/docs/storage-interface.md  |   4 +-
 atr/post/keys.py               |  16 +-
 atr/storage/datatypes.py       |   2 +
 atr/storage/writers/keys.py    | 553 +++++++++++++++++++----------------------
 atr/tasks/keys.py              |   4 +-
 scripts/keys_import.py         |   2 +-
 tests/unit/test_keys_delete.py | 136 ++++++----
 tests/unit/test_keys_writer.py | 218 +++++++++-------
 10 files changed, 503 insertions(+), 444 deletions(-)

diff --git a/atr/admin/__init__.py b/atr/admin/__init__.py
index bf4306f5..441b3799 100644
--- a/atr/admin/__init__.py
+++ b/atr/admin/__init__.py
@@ -1467,7 +1467,9 @@ async def delete_committee_keys_post(
     try:
         async with storage.write(session) as write:
             waca = write.as_committee_admin(committee_key)
-            num_unlinked, num_deleted, publication = await waca.keys.delete_committee_keys()
+            num_unlinked, num_deleted, publication = await waca.keys.delete_committee_keys(
+                f"web:{log.get_request_id()}"
+            )
     except storage.AccessError as e:
         await quart.flash(str(e), "error")
         return await session.redirect(delete_committee_keys_get)
diff --git a/atr/api/__init__.py b/atr/api/__init__.py
index 721113c0..37a7821a 100644
--- a/atr/api/__init__.py
+++ b/atr/api/__init__.py
@@ -728,7 +728,7 @@ async def key_add(
 
     async with storage.write(asf_uid) as write:
         wafc = write.as_foundation_committer()
-        ocr, publications = await wafc.keys.ensure_stored_one(data.key)
+        ocr, publications = await wafc.keys.ensure_stored_one(data.key, f"api:{log.get_request_id()}")
         try:
             key = ocr.result_or_raise()
         except (datatypes.UnknownApacheUidError, ValueError) as e:
@@ -773,7 +773,9 @@ async def key_delete(
     async with storage.write(asf_uid) as write:
         wafc = write.as_foundation_committer()
         # audit_guidance fingerprint ownership verified in storage layer via authenticated user's asfuid
-        oc: outcome.Outcome[datatypes.KeyDeletion] = await wafc.keys.delete_key(fingerprint)
+        oc: outcome.Outcome[datatypes.KeyDeletion] = await wafc.keys.delete_key(
+            fingerprint, f"api:{log.get_request_id()}"
+        )
         deletion = oc.result_or_raise()
 
     warning = shared.keys.publication_removed_warning(deletion.publications)
@@ -838,7 +840,7 @@ async def keys_upload(
     log.keys_submitted("api:keys/upload", filetext, asfuid=asf_uid, committee_keys=[str(selected_committee_key)])
     async with storage.write(asf_uid) as write:
         wacm = write.as_committee_member(str(selected_committee_key))
-        outcomes, publications = await wacm.keys.ensure_associated(filetext)
+        outcomes, publications = await wacm.keys.ensure_associated(filetext, f"api:{log.get_request_id()}")
 
         # TODO: It would be nice to serialise the actual outcomes
         # Or, perhaps better yet, to have a standard datatype mapping
diff --git a/atr/docs/storage-interface.md b/atr/docs/storage-interface.md
index 8b219cca..0635d4ce 100644
--- a/atr/docs/storage-interface.md
+++ b/atr/docs/storage-interface.md
@@ -68,7 +68,7 @@ async with storage.write(asf_uid) as write:
     wafc = write.as_foundation_committer()
 
     # 2. Use the exposed functionality
-    outcome = await wafc.keys.ensure_stored_one(data.key)
+    outcome = await wafc.keys.ensure_stored_one(data.key, source)
 
     # 3. Handle the outcome
     key = outcome.result_or_raise()
@@ -148,7 +148,7 @@ Here is an example from [`post/keys.py`](/ref/atr/post/keys.py) that processes m
 ```python
 async with storage.write() as write:
     wacm = write.as_committee_member(selected_committee)
-    outcomes = await wacm.keys.ensure_associated(keys_text)
+    outcomes = await wacm.keys.ensure_associated(keys_text, source)
 
 success_count = outcomes.result_count
 error_count = outcomes.error_count
diff --git a/atr/post/keys.py b/atr/post/keys.py
index b4dd8624..6baca02a 100644
--- a/atr/post/keys.py
+++ b/atr/post/keys.py
@@ -67,7 +67,7 @@ async def add(
 
         async with storage.write() as write:
             wafc = write.as_foundation_committer()
-            ocr, publications = await wafc.keys.ensure_stored_one(key_text)
+            ocr, publications = await wafc.keys.ensure_stored_one(key_text, f"web:{log.get_request_id()}")
             key = ocr.result_or_raise()
 
             for selected_committee_key in selected_committee_keys:
@@ -89,7 +89,7 @@ async def add(
                 await quart.flash(str(p), "warning")
             elif key.status == datatypes.KeyStatus.REFRESHED:
                 await quart.flash(
-                    f"OpenPGP key {fingerprint_upper} was updated to the version you uploaded.", "success"
+                    f"OpenPGP key {fingerprint_upper} was updated with the version you uploaded.", "success"
                 )
             else:
                 await quart.flash(f"OpenPGP key {fingerprint_upper} added successfully.", "success")
@@ -191,7 +191,11 @@ async def import_selected_revision(
         )
         try:
             outcomes, publications = await wacm.keys.import_keys_file(
-                project_key, version_key, keys_text, release.safe_latest_revision_number
+                project_key,
+                version_key,
+                f"web:{log.get_request_id()}",
+                keys_text,
+                release.safe_latest_revision_number,
             )
         except datatypes.RevisionMismatchError:
             await quart.flash("The draft changed during the import, so its KEYS file was left in place", "error")
@@ -336,7 +340,9 @@ async def _delete_openpgp_key(
 
     async with storage.write() as write:
         wafc = write.as_foundation_committer()
-        oc: outcome.Outcome[datatypes.KeyDeletion] = await wafc.keys.delete_key(fingerprint)
+        oc: outcome.Outcome[datatypes.KeyDeletion] = await wafc.keys.delete_key(
+            fingerprint, f"web:{log.get_request_id()}"
+        )
 
     match oc:
         case outcome.Result(deletion):
@@ -429,7 +435,7 @@ async def _process_keys(keys_text: str, selected_committee: str) -> str:
 
     async with storage.write() as write:
         wacp = write.as_committee_participant(selected_committee)
-        outcomes, publications = await wacp.keys.ensure_associated(keys_text)
+        outcomes, publications = await wacp.keys.ensure_associated(keys_text, f"web:{log.get_request_id()}")
 
     success_count = outcomes.result_count
     error_count = outcomes.error_count
diff --git a/atr/storage/datatypes.py b/atr/storage/datatypes.py
index 2ae2ad64..30e29ad8 100644
--- a/atr/storage/datatypes.py
+++ b/atr/storage/datatypes.py
@@ -48,6 +48,7 @@ class KeyStatus(enum.Flag):
     LINKED = enum.auto()
     # The fingerprint was already stored, but its key block was replaced with a newer one
     REFRESHED = enum.auto()
+    RESTORED = enum.auto()
     INSERTED_AND_LINKED = INSERTED | LINKED
 
 
@@ -55,6 +56,7 @@ class Key(schema.Strict):
     status: KeyStatus
     key_model: sql.SigningCertificate
     member_ids: list[str] = schema.factory(list)
+    input: bytes | None = None
 
 
 class KeysPublish(enum.Enum):
diff --git a/atr/storage/writers/keys.py b/atr/storage/writers/keys.py
index dbd85109..c08240fe 100644
--- a/atr/storage/writers/keys.py
+++ b/atr/storage/writers/keys.py
@@ -36,6 +36,7 @@ import sqlmodel
 
 import atr.cache as cache
 import atr.config as config
+import atr.constants as constants
 import atr.db as db
 import atr.log as log
 import atr.models.basic as basic
@@ -85,6 +86,15 @@ _ALGORITHM_NAMES: Final[dict[int, str]] = {
 }
 _APPROVED_KEY_ALGORITHMS: Final = frozenset({1, 3, 19, 22, 27})
 _EC_MINIMUM_BITS: Final = 255
+_KEY_AUDIT_ACTIONS: Final = (
+    (datatypes.KeyStatus.RESTORED, "key_undelete"),
+    (datatypes.KeyStatus.INSERTED, "key_insert"),
+    (datatypes.KeyStatus.REFRESHED, "key_refresh"),
+    (datatypes.KeyStatus.LINKED, "key_associate_committee"),
+)
+_MAX_CERTIFICATE_BYTES: Final = 1024 * 1024
+_MAX_CERTIFICATE_PLACEMENTS: Final = 16384
+_POINTER_SOURCE_SCHEMES: Final = frozenset({"archive", "svn"})
 _RSA_ALGORITHMS: Final = frozenset({1, 3})
 _RSA_MINIMUM_BITS: Final = 4096
 
@@ -143,24 +153,21 @@ def _deduplicate_keys(roes: list[datatypes.Key | Exception]) -> list[datatypes.K
             continue
         seen.add(fingerprint)
         keys = groups[fingerprint]
-        deduplicated.append(keys[0] if (len(keys) == 1) else _preferred_key(fingerprint, keys))
+        deduplicated.append(keys[0] if (len(keys) == 1) else _merged_key(keys))
     return deduplicated
 
 
-def _preferred_key(fingerprint: str, keys: list[datatypes.Key]) -> datatypes.Key | Exception:
-    candidates: dict[bytes, datatypes.Key] = {}
-    for key in keys:
-        parsed = pgp.certificate_for_fingerprint(key.key_model.ascii_armored_key, fingerprint)
-        if parsed is None:
-            return ValueError(f"Key {fingerprint} could not be read back from its block")
-        candidates.setdefault(parsed.to_bytes(), key)
-    if len(candidates) == 1:
-        return next(iter(candidates.values()))
-    for candidate in candidates.values():
-        others = [other for other in candidates.values() if other is not candidate]
-        if all(_supersedes(fingerprint, candidate, other) for other in others):
-            return candidate
-    return ValueError(f"Key {fingerprint} appears {len(candidates)} times with differing content")
+def _merged_key(keys: list[datatypes.Key]) -> datatypes.Key | Exception:
+    try:
+        block = pgp.merge_certificate_blocks([key.key_model.ascii_armored_key for key in keys])
+    except ValueError as e:
+        return e
+    merged = keys[0]
+    merged.key_model.ascii_armored_key = block
+    merged.key_model.apache_uid = next((key.key_model.apache_uid for key in keys if key.key_model.apache_uid), None)
+    merged.member_ids = sorted({member_id for key in keys for member_id in key.member_ids})
+    merged.input = b"".join(key.input for key in keys if key.input is not None) or None
+    return merged
 
 
 def _signing_key_rows(certificate_fingerprint: str, block: str | bytes) -> list[dict] | None:
@@ -187,14 +194,6 @@ def _signing_key_rows(certificate_fingerprint: str, block: str | bytes) -> list[
     ]
 
 
-def _supersedes(fingerprint: str, candidate: datatypes.Key, other: datatypes.Key) -> bool:
-    candidate_block = candidate.key_model.ascii_armored_key
-    other_block = other.key_model.ascii_armored_key
-    if _block_downgrade_reason(fingerprint, other_block, candidate_block) is not None:
-        return False
-    return _block_downgrade_reason(fingerprint, candidate_block, other_block) is not None
-
-
 async def _sync_signing_keys(data: db.Session, certificates: list[sql.SigningCertificate]) -> None:
     """Bring each certificate's SigningKey rows into line with the block it holds"""
     rows = []
@@ -260,26 +259,16 @@ class FoundationCommitter(GeneralPublic):
         # Specific to this module
         self.__key_block_models_cache = {}
 
-    async def delete_key(self, fingerprint: str) -> outcome.Outcome[datatypes.KeyDeletion]:
+    async def delete_key(self, fingerprint: str, source: str) -> outcome.Outcome[datatypes.KeyDeletion]:
         try:
-            via = sql.validate_instrumented_attribute
+            await self.__data.begin_immediate()
             key = await self.__data.signing_certificate(
                 fingerprint=fingerprint,
                 apache_uid=self.__asf_uid,
                 _committees=True,
             ).demand(storage.AccessError(f"Key not found: {fingerprint}", status=404))
             affected_committee_keys = {committee.key for committee in key.committees}
-            update_result = await self.__data.execute(
-                sqlmodel.update(sql.SigningCertificate)
-                .where(
-                    via(sql.SigningCertificate.fingerprint) == key.fingerprint,
-                    via(sql.SigningCertificate.apache_uid) == self.__asf_uid,
-                    via(sql.SigningCertificate.deleted).is_(None),
-                )
-                .values(deleted=datetime.datetime.now(datetime.UTC))
-            )
-            if getattr(update_result, "rowcount", 0) != 1:
-                raise storage.AccessError(f"Key not found: {fingerprint}", status=404)
+            await self._delete_certificate(key, source)
             await self.__data.commit()
             self.__write_as.append_to_audit_log(
                 action="key_delete",
@@ -297,9 +286,9 @@ class FoundationCommitter(GeneralPublic):
             return outcome.Error(e)
 
     async def ensure_stored_one(
-        self, key_file_text: str
+        self, key_file_text: str, source: str
     ) -> tuple[outcome.Outcome[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
-        return await self.__ensure_one(key_file_text, associate=False)
+        return await self.__ensure_one(key_file_text, source, associate=False)
 
     def public_key_model(
         self,
@@ -329,6 +318,13 @@ class FoundationCommitter(GeneralPublic):
         )
         return await self._keys_file_text(committee)
 
+    async def _delete_certificate(self, certificate: sql.SigningCertificate, source: str) -> None:
+        seq, state = await self.__certificate_chain(certificate.fingerprint, certificate)
+        self.__append_certificate_row(
+            certificate.fingerprint, seq + 1, sql.KeyOperation.DELETE, source, None, state, state
+        )
+        certificate.deleted = datetime.datetime.now(datetime.UTC)
+
     async def _keys_file_text(self, committee: sql.Committee) -> str:
         if not committee.signing_certificates:
             raise storage.AccessError(f"No keys found for committee {committee.key} to generate KEYS file.", status=404)
@@ -392,6 +388,44 @@ class FoundationCommitter(GeneralPublic):
                 suffix_filter=[".asc"],
             )
 
+    async def _revise_certificate(self, key: datatypes.Key, source: str) -> datatypes.KeyStatus:
+        fingerprint = key.key_model.fingerprint
+        existing = await self.__data.signing_certificate(fingerprint=fingerprint, deleted=db.NOT_SET).get()
+        seq, previous = await self.__certificate_chain(fingerprint, existing)
+        blocks = [pgp.certificate_block(previous)] if previous else []
+        block = pgp.merge_certificate_blocks([*blocks, key.key_model.ascii_armored_key])
+        result = pgp.certificate_placements(block)
+        if (len(block) > _MAX_CERTIFICATE_BYTES) or (len(result) > _MAX_CERTIFICATE_PLACEMENTS):
+            raise ValueError(f"Key {fingerprint} would exceed the size admitted for a certificate")
+        changed = result != previous
+        apache_uid = key.key_model.apache_uid or (existing.apache_uid if (existing is not None) else None)
+        head = self.__head_model(fingerprint, block, apache_uid)
+        if changed:
+            raw = None if (source.partition(":")[0] in _POINTER_SOURCE_SCHEMES) else key.input
+            seq = self.__append_certificate_row(
+                fingerprint, seq + 1, sql.KeyOperation.REVISE, source, raw, previous, result
+            )
+        status = datatypes.KeyStatus.REFRESHED if changed else datatypes.KeyStatus.PARSED
+        if existing is None:
+            existing = head
+            self.__data.add(existing)
+            status = datatypes.KeyStatus.INSERTED
+        elif changed:
+            existing.ascii_armored_key = head.ascii_armored_key
+            existing.latest_self_signature = head.latest_self_signature
+            existing.primary_declared_uid = head.primary_declared_uid
+            existing.secondary_declared_uids = head.secondary_declared_uids
+            existing.apache_uid = head.apache_uid
+        # An artifact points at the SigningKey which signed it, so these rows must exist before an
+        # artifact can be attributed
+        if changed or (status == datatypes.KeyStatus.INSERTED):
+            await _sync_signing_keys(self.__data, [existing])
+        if existing.deleted is not None:
+            self.__append_certificate_row(fingerprint, seq + 1, sql.KeyOperation.RESTORE, source, None, result, result)
+            existing.deleted = None
+            status = datatypes.KeyStatus.INSERTED | datatypes.KeyStatus.RESTORED
+        return status
+
     async def _publish_keys_to_svn(
         self, committee: sql.Committee, content: str | None
     ) -> outcome.Outcome[datatypes.KeysPublish]:
@@ -536,6 +570,38 @@ class FoundationCommitter(GeneralPublic):
 
         return datatypes.KeyAssociationUpdate(added=to_add, removed=to_remove, publications=publications)
 
+    def __append_certificate_row(
+        self,
+        fingerprint: str,
+        seq: int,
+        operation: sql.KeyOperation,
+        source: str,
+        raw: bytes | None,
+        previous: frozenset[pgp.Placement],
+        result: frozenset[pgp.Placement],
+        actor: str | None = None,
+        role: sql.KeyRole | None = None,
+        updated: datetime.datetime | None = None,
+    ) -> int:
+        deletions, additions = pgp.delta_fragments(previous, result)
+        if pgp.apply_delta(previous, deletions, additions) != result:
+            raise RuntimeError(f"The delta for {fingerprint} does not reproduce its result")
+        self.__data.add(
+            sql.KeyAttestable(
+                fingerprint=fingerprint,
+                seq=seq,
+                operation=operation,
+                source=source,
+                input=raw,
+                deletions=deletions,
+                additions=additions,
+                updated=updated or datetime.datetime.now(datetime.UTC),
+                actor=actor or self.__asf_uid,
+                role=role or self.__role(),
+            )
+        )
+        return seq
+
     def __block_model(self, key_block: str, ldap_data: cache.EmailUidLookup) -> datatypes.Key:
         # This cache is only held for the session
         if key_block in self.__key_block_models_cache:
@@ -563,87 +629,50 @@ class FoundationCommitter(GeneralPublic):
             status=datatypes.KeyStatus.PARSED,
             key_model=key_model,
             member_ids=sorted(util.openpgp_member_ids(public_key)),
+            input=pgp.certificate_spans(key_block)[0],
         )
 
+    async def __certificate_chain(
+        self, fingerprint: str, certificate: sql.SigningCertificate | None
+    ) -> tuple[int, frozenset[pgp.Placement]]:
+        rows = await self.__data.key_attestable(fingerprint=fingerprint).order_by(sql.KeyAttestable.seq).all()
+        if [row.seq for row in rows] != list(range(1, len(rows) + 1)):
+            raise RuntimeError(f"The log for {fingerprint} is not contiguous")
+        state = pgp.fold_deltas((row.deletions, row.additions) for row in rows)
+        if certificate is None:
+            return len(rows), state
+        if not rows:
+            raise RuntimeError(f"The cached head for {fingerprint} has no log")
+        if state != pgp.certificate_placements(certificate.ascii_armored_key):
+            raise RuntimeError(f"The cached head for {fingerprint} does not match its log")
+        return len(rows), state
+
     async def __database_add_model(
-        self,
-        key: datatypes.Key,
+        self, key: datatypes.Key, source: str
     ) -> tuple[outcome.Outcome[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
-        via = sql.validate_instrumented_attribute
-
         await self.__data.begin_immediate()
-
-        key_values = [key.key_model.model_dump(exclude={"committees"})]
-        key_insert_result = await self.__data.execute(
-            sqlite.insert(sql.SigningCertificate)
-            .values(key_values)
-            .on_conflict_do_nothing(index_elements=["fingerprint"])
-            .returning(via(sql.SigningCertificate.fingerprint))
-        )
-        inserted = key_insert_result.one_or_none() is not None
-        undeleted = []
-        refreshed = False
-        if inserted:
+        status = await self._revise_certificate(key, source)
+        if status == datatypes.KeyStatus.INSERTED:
             await self.__signature_hints_consume([key])
-        else:
-            # The fingerprint only covers the primary packet, so an existing row can hold a stale
-            # block - a since-revoked, re-signed, or re-subkeyed export of the same key. Bring it up
-            # to date, which also carries the apache_uid the undelete below would otherwise set
-            refreshed = await self.__refresh_stored_key(key.key_model)
-            undeleted = await self.__undelete_keys([key.key_model.fingerprint])
-            if undeleted and not refreshed:
-                await self.__data.execute(
-                    sqlmodel.update(sql.SigningCertificate)
-                    .where(via(sql.SigningCertificate.fingerprint) == key.key_model.fingerprint)
-                    .values(apache_uid=key.key_model.apache_uid)
-                )
-        # A refused downgrade kept the stored block, so its rows must not be rebuilt from the block we
-        # declined; only sync when the block actually changed
-        if inserted or refreshed:
-            await _sync_signing_keys(self.__data, [key.key_model])
         await self.__data.commit()
-
-        if undeleted:
-            self.__write_as.append_to_audit_log(
-                action="key_undelete",
-                asf_uid=self.__asf_uid,
-                fingerprints=[f for f in undeleted],
-            )
-            publications = await self.__sync_committees_for_keys(undeleted)
-            log.info(f"Undeleted key {key.key_model.fingerprint}")
-            undeleted_key = datatypes.Key(status=datatypes.KeyStatus.INSERTED, key_model=key.key_model)
-            return outcome.Result(undeleted_key), publications
-
-        if refreshed:
-            self.__write_as.append_to_audit_log(
-                action="key_refresh",
-                asf_uid=self.__asf_uid,
-                fingerprint=key.key_model.fingerprint,
-                key_apache_uid=key.key_model.apache_uid,
-            )
-            # A changed block can flip an existing signature check, so let its committees catch up
-            publications = await self.__sync_committees_for_keys([key.key_model.fingerprint])
-            log.info(f"Refreshed stored key {key.key_model.fingerprint}")
-            refreshed_key = datatypes.Key(status=datatypes.KeyStatus.REFRESHED, key_model=key.key_model)
-            return outcome.Result(refreshed_key), publications
-
-        if not inserted:
-            log.info(f"Key {key.key_model.fingerprint} already exists in database")
-            return outcome.Result(datatypes.Key(status=datatypes.KeyStatus.PARSED, key_model=key.key_model)), {}
-
-        log.info(f"Inserted key {key.key_model.fingerprint}")
+        fingerprint = key.key_model.fingerprint
+        if status == datatypes.KeyStatus.PARSED:
+            log.info(f"Key {fingerprint} already exists in database")
+            return outcome.Result(datatypes.Key(status=status, key_model=key.key_model)), {}
+        action = next(action for flag, action in _KEY_AUDIT_ACTIONS if status & flag)
         self.__write_as.append_to_audit_log(
-            action="key_insert",
+            action=action,
             asf_uid=self.__asf_uid,
-            fingerprint=key.key_model.fingerprint,
+            fingerprint=fingerprint,
             key_apache_uid=key.key_model.apache_uid,
         )
-
-        # TODO: PARSED now acts as "ALREADY_ADDED"
-        return outcome.Result(datatypes.Key(status=datatypes.KeyStatus.INSERTED, key_model=key.key_model)), {}
+        # A changed block can flip an existing signature check, so let its committees catch up
+        publications = await self.__sync_committees_for_keys([fingerprint])
+        log.info(f"Stored key {fingerprint}")
+        return outcome.Result(datatypes.Key(status=status, key_model=key.key_model)), publications
 
     async def __ensure_one(
-        self, key_file_text: str, associate: bool = True
+        self, key_file_text: str, source: str, associate: bool = True
     ) -> tuple[outcome.Outcome[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
         try:
             key_blocks = util.parse_key_blocks(key_file_text)
@@ -667,7 +696,22 @@ class FoundationCommitter(GeneralPublic):
                     "OpenPGP key could not be associated with an ASF UID. Import it through a KEYS file instead."
                 )
             ), {}
-        return await self.__database_add_model(key)
+        try:
+            return await self.__database_add_model(key, source)
+        except ValueError as e:
+            return outcome.Error(e), {}
+
+    def __head_model(self, fingerprint: str, block: str, apache_uid: str | None) -> sql.SigningCertificate:
+        key, _ = openpgp.composed.SignedPublicKey.from_armor(block)
+        uids = list(key.user_ids)
+        return sql.SigningCertificate(
+            fingerprint=fingerprint,
+            latest_self_signature=pgp.latest_self_signature_created_at(key),
+            primary_declared_uid=uids[0] if uids else None,
+            secondary_declared_uids=uids[1:],
+            apache_uid=apache_uid,
+            ascii_armored_key=block,
+        )
 
     async def __keys_file_format(
         self,
@@ -716,21 +760,12 @@ and was published by the committee.\
         full_keys_file_content = header_content + key_blocks_str
         return full_keys_file_content
 
-    async def __refresh_stored_key(self, model: sql.SigningCertificate) -> bool:
-        existing = await self.__data.signing_certificate(fingerprint=model.fingerprint, deleted=db.NOT_SET).get()
-        if (existing is None) or (existing.ascii_armored_key == model.ascii_armored_key):
-            return False
-        downgrade = _block_downgrade_reason(model.fingerprint, existing.ascii_armored_key, model.ascii_armored_key)
-        if downgrade is not None:
-            log.warning(f"Not refreshing {model.fingerprint}: the uploaded block {downgrade}")
-            return False
-        existing.ascii_armored_key = model.ascii_armored_key
-        existing.latest_self_signature = model.latest_self_signature
-        existing.primary_declared_uid = model.primary_declared_uid
-        existing.secondary_declared_uids = model.secondary_declared_uids
-        existing.apache_uid = model.apache_uid
-        self.__data.add(existing)
-        return True
+    def __role(self) -> sql.KeyRole:
+        if self.__asf_uid == constants.SYSTEM_SERVICE_UID:
+            return sql.KeyRole.SERVICE
+        if isinstance(self.__write_as, storage.WriteAsFoundationAdmin | storage.WriteAsCommitteeAdmin):
+            return sql.KeyRole.ADMIN
+        return sql.KeyRole.USER
 
     async def __signature_hints_consume(self, keys: list[datatypes.Key]) -> list[str]:
         via = sql.validate_instrumented_attribute
@@ -805,21 +840,6 @@ and was published by the committee.\
                 return ldap_data[email]
         return None
 
-    async def __undelete_keys(self, fingerprints: list[str]) -> list[str]:
-        if not fingerprints:
-            return []
-        via = sql.validate_instrumented_attribute
-        result = await self.__data.execute(
-            sqlmodel.update(sql.SigningCertificate)
-            .where(
-                via(sql.SigningCertificate.fingerprint).in_(sorted(fingerprints)),
-                via(sql.SigningCertificate.deleted).is_not(None),
-            )
-            .values(deleted=None)
-            .returning(via(sql.SigningCertificate.fingerprint))
-        )
-        return sorted(result.scalars().all())
-
 
 class CommitteeParticipant(FoundationCommitter):
     def __init__(
@@ -907,9 +927,9 @@ class CommitteeParticipant(FoundationCommitter):
         return self.__committee_key
 
     async def ensure_associated(
-        self, keys_file_text: str
+        self, keys_file_text: str, source: str
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
-        outcomes, publications = await self.__ensure(keys_file_text, associate=True)
+        outcomes, publications = await self.__ensure(keys_file_text, source, associate=True)
         if not outcomes.any_result:
             return outcomes, publications
         _, publication = await self.autogenerate_keys_file()
@@ -917,9 +937,9 @@ class CommitteeParticipant(FoundationCommitter):
         return outcomes, publications
 
     async def ensure_stored(
-        self, keys_file_text: str
+        self, keys_file_text: str, source: str
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
-        outcomes, publications = await self.__ensure(keys_file_text, associate=False)
+        outcomes, publications = await self.__ensure(keys_file_text, source, associate=False)
         if not outcomes.any_result:
             return outcomes, publications
         _, publication = await self.autogenerate_keys_file()
@@ -930,6 +950,7 @@ class CommitteeParticipant(FoundationCommitter):
         self,
         project_key: safe.ProjectKey,
         version_key: safe.VersionKey,
+        source: str,
         keys_file_text: str | None = None,
         expected_revision: safe.RevisionNumber | None = None,
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
@@ -950,7 +971,7 @@ class CommitteeParticipant(FoundationCommitter):
                 status=403,
             )
 
-        outcomes, publications = await self.ensure_associated(keys_file_text)
+        outcomes, publications = await self.ensure_associated(keys_file_text, source)
         release_keys_removed = False
         # Remove the KEYS file if 100% imported
         if (outcomes.result_count > 0) and (outcomes.error_count == 0):
@@ -982,23 +1003,40 @@ class CommitteeParticipant(FoundationCommitter):
         )
         return outcomes, publications
 
+    def __audit_certificate(self, key: datatypes.Key, status: datatypes.KeyStatus) -> None:
+        action = next((action for flag, action in _KEY_AUDIT_ACTIONS if status & flag), None)
+        if action is None:
+            return
+        self.__write_as.append_to_audit_log(
+            action=action,
+            asf_uid=self.__asf_uid,
+            committee_key=self.__committee_key,
+            fingerprint=key.key_model.fingerprint,
+            key_apache_uid=key.key_model.apache_uid,
+            linked=bool(status & datatypes.KeyStatus.LINKED),
+        )
+
     def __block_models(self, key_block: str, ldap_data: cache.EmailUidLookup) -> list[datatypes.Key | Exception]:
         try:
-            public_keys, _ = openpgp.composed.SignedPublicKey.from_armor_many(key_block)
+            blocks = pgp.certificate_blocks(key_block)
+            spans = pgp.certificate_spans(key_block)
         except Exception as e:
             raise ValueError(f"Error loading OpenPGP key block: {e}") from e
-        if not public_keys:
+        if not blocks:
             raise ValueError("Error loading OpenPGP key block: no keys found")
-        original_key_block = key_block if (len(public_keys) == 1) else None
-        key_list = []
-        for public_key in public_keys:
+        key_list: list[datatypes.Key | Exception] = []
+        for block, span in zip(blocks, spans, strict=True):
             try:
-                key_model = self.public_key_model(public_key, ldap_data, original_key_block=original_key_block)
-                _validate_key_strength(public_key)
+                public_keys, _ = openpgp.composed.SignedPublicKey.from_armor_many(block)
+                if len(public_keys) != 1:
+                    raise ValueError(f"Expected one certificate in the block, got {len(public_keys)}")
+                key_model = self.public_key_model(public_keys[0], ldap_data, original_key_block=block)
+                _validate_key_strength(public_keys[0])
                 key = datatypes.Key(
                     status=datatypes.KeyStatus.PARSED,
                     key_model=key_model,
-                    member_ids=sorted(util.openpgp_member_ids(public_key)),
+                    member_ids=sorted(util.openpgp_member_ids(public_keys[0])),
+                    input=span,
                 )
                 key_list.append(key)
             except Exception as e:
@@ -1006,12 +1044,12 @@ class CommitteeParticipant(FoundationCommitter):
         return key_list
 
     async def __database_add_models(
-        self, outcomes: outcome.List[datatypes.Key], associate: bool = True
+        self, outcomes: outcome.List[datatypes.Key], source: str, associate: bool = True
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
         # Try to upsert all models and link to the committee in one transaction
         publications: dict[str, outcome.Outcome[datatypes.KeysPublish]] = {}
         try:
-            outcomes, publications = await self.__database_add_models_core(outcomes, associate=associate)
+            outcomes, publications = await self.__database_add_models_core(outcomes, source, associate=associate)
         except Exception as e:
             # This logging is just so that ruff does not erase e
             log.info(f"Post-parse error: {e}")
@@ -1025,141 +1063,64 @@ class CommitteeParticipant(FoundationCommitter):
             outcomes.update_roes(Exception, raise_post_parse_error)
         return outcomes, publications
 
-    async def __database_add_models_core(  # noqa: C901
+    async def __database_add_models_core(
         self,
         outcomes: outcome.List[datatypes.Key],
+        source: str,
         associate: bool = True,
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
-        via = sql.validate_instrumented_attribute
         key_list = outcomes.results()
 
-        await self.__data.begin_immediate()
-        committee = await self.committee()
-
-        key_values = [key.key_model.model_dump(exclude={"committees"}) for key in key_list]
-        incoming_blocks = {v["fingerprint"]: v["ascii_armored_key"] for v in key_values}
-        stored_blocks: dict[str, str] = {}
-        if incoming_blocks:
-            stored_result = await self.__data.execute(
-                sqlmodel.select(
-                    via(sql.SigningCertificate.fingerprint),
-                    via(sql.SigningCertificate.ascii_armored_key),
-                ).where(via(sql.SigningCertificate.fingerprint).in_(sorted(incoming_blocks)))
-            )
-            stored_blocks = {fingerprint: block for fingerprint, block in stored_result.all()}
-        # A key we already hold whose incoming block differs has been re-imported with a newer state,
-        # which can flip an existing signature check even when nothing about the association changed
-        refreshed_fingerprints = {
-            fingerprint
-            for fingerprint, block in incoming_blocks.items()
-            if (fingerprint in stored_blocks) and (stored_blocks[fingerprint] != block)
-        }
-        # A re-import must not roll a held key's effective state back, so leave any block which would do
-        # so untouched rather than overwrite the stored one
-        downgraded = {
-            fingerprint
-            for fingerprint in refreshed_fingerprints
-            if _block_downgrade_reason(fingerprint, stored_blocks[fingerprint], incoming_blocks[fingerprint])
-            is not None
-        }
-        if downgraded:
-            kept = ", ".join(sorted(downgraded))
-            log.warning(f"Keeping the stored block for {util.plural(len(downgraded), 'key')}: {kept}")
-            key_list = [key for key in key_list if key.key_model.fingerprint not in downgraded]
-            key_values = [values for values in key_values if values["fingerprint"] not in downgraded]
-        if key_values:
-            stmt = sqlite.insert(sql.SigningCertificate).values(key_values)
-            # The fingerprint only covers the primary packet, so a re-import can carry a newer block
-            # for a key we already hold - a revocation, an extended expiry, a fresh subkey. Refresh
-            # the whole derived row, not just the apache_uid
-            stmt = stmt.on_conflict_do_update(
-                index_elements=["fingerprint"],
-                set_={
-                    "apache_uid": stmt.excluded.apache_uid,
-                    "ascii_armored_key": stmt.excluded.ascii_armored_key,
-                    "latest_self_signature": stmt.excluded.latest_self_signature,
-                    "primary_declared_uid": stmt.excluded.primary_declared_uid,
-                    "secondary_declared_uids": stmt.excluded.secondary_declared_uids,
-                },
-            )
-            key_insert_result = await self.__data.execute(
-                stmt.returning(via(sql.SigningCertificate.fingerprint)),
-            )
-            key_inserts = {row.fingerprint for row in key_insert_result}
-            log.info(f"Inserted or updated {util.plural(len(key_inserts), 'key')}")
-        else:
-            # TODO: Warn the user about any keys that were already inserted
-            key_inserts = set()
-            log.info("Inserted 0 keys (no keys to insert)")
-
-        def replace_with_inserted(key: datatypes.Key) -> datatypes.Key:
-            if key.key_model.fingerprint in key_inserts:
-                key.status = datatypes.KeyStatus.INSERTED
+        await self.__data.committee(key=self.__committee_key).demand(
+            storage.AccessError(f"Committee not found: {self.__committee_key}", status=404)
+        )
+
+        statuses: dict[str, datatypes.KeyStatus] = {}
+        failures: dict[str, Exception] = {}
+        for key in key_list:
+            fingerprint = key.key_model.fingerprint
+            try:
+                await self.__data.begin_immediate()
+                status = await self._revise_certificate(key, source)
+                await self.__signature_hints_consume([key])
+                linked = associate and (await self.__link_certificate(fingerprint))
+                await self.__data.commit()
+            except Exception as e:
+                await self.__data.rollback()
+                failures[fingerprint] = e
+                continue
+            if linked:
+                status |= datatypes.KeyStatus.LINKED
+            statuses[fingerprint] = status
+            self.__audit_certificate(key, status)
+        log.info(f"Stored {util.plural(len(statuses), 'key')}")
+
+        def replace_with_status(key: datatypes.Key) -> datatypes.Key:
+            fingerprint = key.key_model.fingerprint
+            if fingerprint in failures:
+                raise datatypes.PublicKeyError(key, failures[fingerprint])
+            key.status = statuses[fingerprint]
             return key
 
-        outcomes.update_roes(Exception, replace_with_inserted)
+        outcomes.update_roes(Exception, replace_with_status)
 
-        persisted_fingerprints = {v["fingerprint"] for v in key_values} | downgraded
-        # An artifact points at the SigningKey which signed it, so these rows must exist before an
-        # artifact can be attributed
-        await _sync_signing_keys(self.__data, [key.key_model for key in key_list])
-        undeleted = await self.__undelete_keys(sorted(persisted_fingerprints))
-        await self.__signature_hints_consume(key_list)
-        await self.__data.flush()
-
-        existing_fingerprints = {k.fingerprint for k in committee.signing_certificates}
-        new_fingerprints = persisted_fingerprints - existing_fingerprints
-        link_inserts = set()
-        if new_fingerprints and associate:
-            link_values = [{"committee_key": self.__committee_key, "key_fingerprint": fp} for fp in new_fingerprints]
-            link_insert_result = await self.__data.execute(
-                sqlite.insert(sql.KeyLink)
-                .values(link_values)
-                .on_conflict_do_nothing(index_elements=["committee_key", "key_fingerprint"])
-                .returning(via(sql.KeyLink.key_fingerprint))
-            )
-            link_inserts = {row.key_fingerprint for row in link_insert_result}
-            log.info(f"Inserted {util.plural(len(link_inserts), 'key link')}")
-
-            def replace_with_linked(key: datatypes.Key) -> datatypes.Key:
-                # nonlocal link_inserts
-                match key:
-                    case datatypes.Key(status=datatypes.KeyStatus.INSERTED):
-                        if key.key_model.fingerprint in link_inserts:
-                            key.status = datatypes.KeyStatus.INSERTED_AND_LINKED
-                    case datatypes.Key(status=datatypes.KeyStatus.PARSED):
-                        if key.key_model.fingerprint in link_inserts:
-                            key.status = datatypes.KeyStatus.LINKED
-                return key
-
-            outcomes.update_roes(Exception, replace_with_linked)
-        else:
-            log.info("Inserted 0 key links (none to insert)")
-
-        await self.__data.commit()
-        if key_inserts or link_inserts:
-            self.__write_as.append_to_audit_log(
-                action="key_insert_or_associate_bulk",
-                asf_uid=self.__asf_uid,
-                committee_key=self.__committee_key,
-                inserted_fingerprints=sorted(key_inserts),
-                linked_fingerprints=sorted(link_inserts),
-            )
+        changed = sorted(
+            fingerprint for fingerprint, status in statuses.items() if status != datatypes.KeyStatus.PARSED
+        )
+        restored = sorted(
+            fingerprint for fingerprint, status in statuses.items() if status & datatypes.KeyStatus.RESTORED
+        )
         publications: dict[str, outcome.Outcome[datatypes.KeysPublish]] = {}
-        if undeleted:
-            self.__write_as.append_to_audit_log(
-                action="key_undelete",
-                asf_uid=self.__asf_uid,
-                fingerprints=[f for f in undeleted],
-            )
-            publications = await self.__sync_committees_for_keys(undeleted)
-        if link_inserts or (refreshed_fingerprints - downgraded):
+        if restored:
+            publications = await self.__sync_committees_for_keys(restored)
+        if changed:
             await self._recheck_committee_drafts(self.__committee_key)
         return outcomes, publications
 
     async def __ensure(
         self,
         keys_file_text: str,
+        source: str,
         associate: bool = True,
     ) -> tuple[outcome.List[datatypes.Key], dict[str, outcome.Outcome[datatypes.KeysPublish]]]:
         outcomes = outcome.List[datatypes.Key]()
@@ -1186,7 +1147,17 @@ class CommitteeParticipant(FoundationCommitter):
         outcomes.extend_roes(Exception, await asyncio.to_thread(_deduplicate_keys, roes))
         # Try adding the keys to the database
         # If not, all keys will be replaced with a PostParseError
-        return await self.__database_add_models(outcomes, associate=associate)
+        return await self.__database_add_models(outcomes, source, associate=associate)
+
+    async def __link_certificate(self, fingerprint: str) -> bool:
+        via = sql.validate_instrumented_attribute
+        link_insert_result = await self.__data.execute(
+            sqlite.insert(sql.KeyLink)
+            .values([{"committee_key": self.__committee_key, "key_fingerprint": fingerprint}])
+            .on_conflict_do_nothing(index_elements=["committee_key", "key_fingerprint"])
+            .returning(via(sql.KeyLink.key_fingerprint))
+        )
+        return link_insert_result.one_or_none() is not None
 
     async def __signature_hints_consume(self, keys: list[datatypes.Key]) -> list[str]:
         via = sql.validate_instrumented_attribute
@@ -1230,21 +1201,6 @@ class CommitteeParticipant(FoundationCommitter):
         await self._recheck_committee_drafts(*committee_keys)
         return publications
 
-    async def __undelete_keys(self, fingerprints: list[str]) -> list[str]:
-        if not fingerprints:
-            return []
-        via = sql.validate_instrumented_attribute
-        result = await self.__data.execute(
-            sqlmodel.update(sql.SigningCertificate)
-            .where(
-                via(sql.SigningCertificate.fingerprint).in_(sorted(fingerprints)),
-                via(sql.SigningCertificate.deleted).is_not(None),
-            )
-            .values(deleted=None)
-            .returning(via(sql.SigningCertificate.fingerprint))
-        )
-        return sorted(result.scalars().all())
-
 
 class CommitteeMember(CommitteeParticipant):
     def __init__(
@@ -1302,7 +1258,9 @@ class FoundationAdmin(CommitteeMember):
     def committee_key(self) -> str:
         return self.__committee_key
 
-    async def delete_committee_keys(self) -> tuple[int, int, outcome.Outcome[datatypes.KeysPublish] | None]:
+    async def delete_committee_keys(
+        self, source: str
+    ) -> tuple[int, int, outcome.Outcome[datatypes.KeysPublish] | None]:
         via = sql.validate_instrumented_attribute
         await self.__data.committee(key=self.__committee_key).demand(
             storage.AccessError(f"Committee not found: {self.__committee_key}", status=404)
@@ -1330,17 +1288,16 @@ class FoundationAdmin(CommitteeMember):
         still_linked = set(still_linked_rows.scalars().all())
         orphaned = [fingerprint for fingerprint in unlinked if fingerprint not in still_linked]
 
-        num_deleted = 0
-        if orphaned:
-            update_result = await self.__data.execute(
-                sqlmodel.update(sql.SigningCertificate)
-                .where(
-                    via(sql.SigningCertificate.fingerprint).in_(orphaned),
-                    via(sql.SigningCertificate.deleted).is_(None),
-                )
-                .values(deleted=datetime.datetime.now(datetime.UTC))
+        orphaned_rows = await self.__data.execute(
+            sqlmodel.select(sql.SigningCertificate).where(
+                via(sql.SigningCertificate.fingerprint).in_(orphaned),
+                via(sql.SigningCertificate.deleted).is_(None),
             )
-            num_deleted = getattr(update_result, "rowcount", 0)
+        )
+        num_deleted = 0
+        for certificate in orphaned_rows.scalars().all():
+            await self._delete_certificate(certificate, source)
+            num_deleted += 1
 
         await self.__data.commit()
         self.__write_as.append_to_audit_log(
diff --git a/atr/tasks/keys.py b/atr/tasks/keys.py
index 3ea60d4d..9074b042 100644
--- a/atr/tasks/keys.py
+++ b/atr/tasks/keys.py
@@ -26,7 +26,9 @@ async def import_file(task_args: args.ImportFile) -> results.Results | None:
     """Import a KEYS file from a draft release candidate revision."""
     async with storage.write(task_args.asf_uid) as write:
         wacm = await write.as_project_committee_member(task_args.project_key)
-        outcomes, _ = await wacm.keys.import_keys_file(task_args.project_key, task_args.version_key)
+        outcomes, _ = await wacm.keys.import_keys_file(
+            task_args.project_key, task_args.version_key, "task:keys_import_file"
+        )
         if outcomes.any_error:
             # TODO: Log this? This code is unused anyway
             pass
diff --git a/scripts/keys_import.py b/scripts/keys_import.py
index f91e5854..3486ce87 100755
--- a/scripts/keys_import.py
+++ b/scripts/keys_import.py
@@ -289,7 +289,7 @@ async def _apply_committee(asf_uid: str, committee_key: str, selections: list[Se
     async with storage.write(asf_uid) as write:
         waca = write.as_committee_admin(committee_key)
         selected_text = "\n\n".join(selection.armored for selection in selections)
-        outcomes, publications = await waca.keys.ensure_associated(selected_text)
+        outcomes, publications = await waca.keys.ensure_associated(selected_text, f"downloads:{committee_key}/KEYS")
         log_outcome_errors(outcomes, committee_key)
         disabled = datatypes.KeysPublish.AUTOMATION_DISABLED
         for name, publication in sorted(publications.items()):
diff --git a/tests/unit/test_keys_delete.py b/tests/unit/test_keys_delete.py
index 652ce368..e9dde930 100644
--- a/tests/unit/test_keys_delete.py
+++ b/tests/unit/test_keys_delete.py
@@ -29,12 +29,16 @@ import sqlmodel
 
 import atr.db as db
 import atr.models.sql as sql
+import atr.pgp as pgp
 import atr.storage.datatypes as datatypes
 import atr.storage.writers.keys as keys_writer
 import atr.tasks.checks as checks
+import tests.unit.pgp_fixtures as pgp_fixtures
 
-ALPHA_FINGERPRINT = "a" * 40
-BETA_FINGERPRINT = "b" * 40
+ALPHA_BLOCK = pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC
+ALPHA_FINGERPRINT = pgp_fixtures.REVOKED_SUBKEY_PRIMARY_FINGERPRINT
+BETA_BLOCK = pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC
+BETA_FINGERPRINT = pgp_fixtures.EXPIRED_SUBKEY_PRIMARY_FINGERPRINT
 
 
 @pytest.fixture
@@ -55,13 +59,13 @@ async def test_committee_signing_keys_exclude_deleted(sqlite_sessionmaker, monke
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
         writer = _committer_writer(data)
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
+        (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")).result_or_raise()
     monkeypatch.setattr(checks.db, "session", lambda: sqlite_sessionmaker())
     release = SimpleNamespace(committee=SimpleNamespace(key="tooling"))
 
     signing_keys = await checks._resolve_committee_signing_keys(release)
 
-    assert signing_keys == [f"{BETA_FINGERPRINT}:armored beta"]
+    assert signing_keys == [f"{BETA_FINGERPRINT}:{BETA_BLOCK}"]
 
 
 async def test_delete_committee_keys_deletes_orphans(sqlite_sessionmaker) -> None:
@@ -72,7 +76,7 @@ async def test_delete_committee_keys_deletes_orphans(sqlite_sessionmaker) -> Non
         await data.commit()
         writer = _admin_writer(data)
 
-        num_unlinked, num_deleted, _ = await writer.delete_committee_keys()
+        num_unlinked, num_deleted, _ = await writer.delete_committee_keys("web:req-1")
 
         assert (num_unlinked, num_deleted) == (2, 1)
         alpha = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT, deleted=db.NOT_SET).get()
@@ -88,15 +92,20 @@ async def test_delete_key_retains_row_and_links(sqlite_sessionmaker) -> None:
         await _seed_keys(data)
         writer = _committer_writer(data)
 
-        oc = await writer.delete_key(ALPHA_FINGERPRINT)
+        oc = await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")
 
         oc.result_or_raise()
         assert await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT).get() is None
         key = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT, deleted=True).get()
         assert key is not None
-        assert key.ascii_armored_key == "armored alpha"
+        assert key.ascii_armored_key == ALPHA_BLOCK
         links = await data.execute(sqlmodel.select(sql.KeyLink).where(sql.KeyLink.key_fingerprint == ALPHA_FINGERPRINT))
         assert len(links.all()) == 1
+        rows = await data.key_attestable(fingerprint=ALPHA_FINGERPRINT).all()
+        assert [(row.seq, row.operation, row.source) for row in rows] == [
+            (1, sql.KeyOperation.REVISE, "web:seed"),
+            (2, sql.KeyOperation.DELETE, "web:req-1"),
+        ]
 
 
 async def test_delete_key_twice_errors(sqlite_sessionmaker) -> None:
@@ -104,8 +113,8 @@ async def test_delete_key_twice_errors(sqlite_sessionmaker) -> None:
         await _seed_keys(data)
         writer = _committer_writer(data)
 
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
-        second = await writer.delete_key(ALPHA_FINGERPRINT)
+        (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")).result_or_raise()
+        second = await writer.delete_key(ALPHA_FINGERPRINT, "web:req-2")
 
         assert second.error_or_none() is not None
 
@@ -114,7 +123,7 @@ async def test_deleted_key_hidden_from_committee_relationship(sqlite_sessionmake
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
         writer = _committer_writer(data)
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
+        (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")).result_or_raise()
 
         committee = (
             (
@@ -135,12 +144,12 @@ async def test_keys_file_text_excludes_deleted(sqlite_sessionmaker) -> None:
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
         writer = _committer_writer(data)
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
+        (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")).result_or_raise()
 
         text = await writer.keys_file_text("tooling")
 
-        assert "armored beta" in text
-        assert "armored alpha" not in text
+        assert BETA_FINGERPRINT.upper() in text
+        assert ALPHA_FINGERPRINT.upper() not in text
 
 
 async def test_restrict_prevents_hard_delete_of_referenced_key(sqlite_sessionmaker) -> None:
@@ -170,58 +179,98 @@ async def test_reupload_undeletes_and_restores_associations(sqlite_sessionmaker)
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
         writer = _committer_writer(data)
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
+        (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-1")).result_or_raise()
         key = datatypes.Key(
             status=datatypes.KeyStatus.PARSED,
-            key_model=_key(ALPHA_FINGERPRINT, "alice", "armored alpha"),
+            key_model=_key(ALPHA_FINGERPRINT, "alice", ALPHA_BLOCK),
         )
 
-        oc, publications = await writer._FoundationCommitter__database_add_model(key)
+        oc, publications = await writer._FoundationCommitter__database_add_model(key, "web:req-2")
 
-        assert oc.result_or_raise().status == datatypes.KeyStatus.INSERTED
+        assert oc.result_or_raise().status == datatypes.KeyStatus.INSERTED | datatypes.KeyStatus.RESTORED
         assert set(publications) == {"tooling"}
         row = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT).get()
         assert row is not None
         assert row.deleted is None
         committee = await data.committee(key="tooling", _signing_certificates=True).get()
         assert committee is not None
-        assert sorted(k.fingerprint for k in committee.signing_certificates) == [ALPHA_FINGERPRINT, BETA_FINGERPRINT]
+        assert sorted(k.fingerprint for k in committee.signing_certificates) == sorted(
+            [ALPHA_FINGERPRINT, BETA_FINGERPRINT]
+        )
+        rows = await data.key_attestable(fingerprint=ALPHA_FINGERPRINT).all()
+        assert [(row.seq, row.operation, row.source) for row in rows] == [
+            (1, sql.KeyOperation.REVISE, "web:seed"),
+            (2, sql.KeyOperation.DELETE, "web:req-1"),
+            (3, sql.KeyOperation.RESTORE, "web:req-2"),
+        ]
 
 
 async def test_reupload_with_a_changed_block_refreshes_it(sqlite_sessionmaker) -> None:
+    stripped = pgp_fixtures.block_without_signature_type(ALPHA_BLOCK, 0x28)
     async with sqlite_sessionmaker() as data:
-        await _seed_keys(data)
+        await _seed_keys(data, alpha_block=stripped)
         writer = _committer_writer(data)
         key = datatypes.Key(
             status=datatypes.KeyStatus.PARSED,
-            key_model=_key(ALPHA_FINGERPRINT, "alice", "armored alpha revoked"),
+            key_model=_key(ALPHA_FINGERPRINT, "alice", ALPHA_BLOCK),
+            input=pgp._dearmored(ALPHA_BLOCK),
         )
 
-        oc, _publications = await writer._FoundationCommitter__database_add_model(key)
+        oc, _publications = await writer._FoundationCommitter__database_add_model(key, "web:req-1")
 
         assert oc.result_or_raise().status == datatypes.KeyStatus.REFRESHED
         row = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT).get()
         assert row is not None
-        assert row.ascii_armored_key == "armored alpha revoked"
+        assert row.ascii_armored_key == pgp.merge_certificate_blocks([ALPHA_BLOCK])
+        rows = await data.key_attestable(fingerprint=ALPHA_FINGERPRINT).all()
+        assert [(row.seq, row.source, row.deletions, row.input) for row in rows] == [
+            (1, "web:seed", None, None),
+            (2, "web:req-1", None, pgp._dearmored(ALPHA_BLOCK)),
+        ]
+        assert rows[1].additions is not None
+        assert [tag for tag, _ in pgp._frames(rows[1].additions)] == [14, 2]
+        assert pgp.fold_deltas((row.deletions, row.additions) for row in rows) == pgp.certificate_placements(
+            row.ascii_armored_key
+        )
         # A changed block can flip a signature check, so the key's committees must be rechecked
         writer._recheck_committee_drafts.assert_awaited()
 
 
-async def test_reupload_with_an_unchanged_block_is_a_noop(sqlite_sessionmaker) -> None:
+async def test_reupload_with_a_subset_block_writes_no_observation(sqlite_sessionmaker) -> None:
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
         writer = _committer_writer(data)
         key = datatypes.Key(
             status=datatypes.KeyStatus.PARSED,
-            key_model=_key(ALPHA_FINGERPRINT, "alice", "armored alpha"),
+            key_model=_key(ALPHA_FINGERPRINT, "alice", pgp_fixtures.block_without_signature_type(ALPHA_BLOCK, 0x28)),
         )
 
-        oc, _publications = await writer._FoundationCommitter__database_add_model(key)
+        oc, _publications = await writer._FoundationCommitter__database_add_model(key, "web:req-1")
 
         assert oc.result_or_raise().status == datatypes.KeyStatus.PARSED
+        row = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT).get()
+        assert row is not None
+        assert row.ascii_armored_key == ALPHA_BLOCK
+        assert [row.seq for row in await data.key_attestable(fingerprint=ALPHA_FINGERPRINT).all()] == [1]
         writer._recheck_committee_drafts.assert_not_awaited()
 
 
+async def test_write_to_a_head_without_log_is_refused(sqlite_sessionmaker) -> None:
+    async with sqlite_sessionmaker() as data:
+        data.add(sql.Committee(key="tooling"))
+        data.add(_key(ALPHA_FINGERPRINT, "alice", ALPHA_BLOCK))
+        await data.commit()
+        writer = _committer_writer(data)
+        key = datatypes.Key(
+            status=datatypes.KeyStatus.PARSED,
+            key_model=_key(ALPHA_FINGERPRINT, "alice", ALPHA_BLOCK),
+        )
+
+        with pytest.raises(RuntimeError, match="has no log"):
+            await writer._FoundationCommitter__database_add_model(key, "web:req-1")
+        assert (await writer.delete_key(ALPHA_FINGERPRINT, "web:req-2")).error_or_none() is not None
+
+
 async def test_signature_hints_consume_flags_and_deletes(sqlite_sessionmaker) -> None:
     async with sqlite_sessionmaker() as data:
         await _seed_keys(data)
@@ -248,21 +297,6 @@ async def test_signature_hints_consume_flags_and_deletes(sqlite_sessionmaker) ->
         assert await data.signature_hint(hint="feedfacefeedface").get() is not None
 
 
-async def test_undelete_keys_clears_deleted(sqlite_sessionmaker) -> None:
-    async with sqlite_sessionmaker() as data:
-        await _seed_keys(data)
-        writer = _committer_writer(data)
-        (await writer.delete_key(ALPHA_FINGERPRINT)).result_or_raise()
-
-        undeleted = await writer._FoundationCommitter__undelete_keys([ALPHA_FINGERPRINT, BETA_FINGERPRINT])
-        await data.commit()
-
-        assert undeleted == [ALPHA_FINGERPRINT]
-        key = await data.signing_certificate(fingerprint=ALPHA_FINGERPRINT).get()
-        assert key is not None
-        assert key.deleted is None
-
-
 def _admin_writer(data: db.Session) -> keys_writer.FoundationAdmin:
     write = mock.MagicMock()
     write.authorisation.asf_uid = "alice"
@@ -305,10 +339,26 @@ def _key(fingerprint: str, apache_uid: str, armored: str) -> sql.SigningCertific
     )
 
 
-async def _seed_keys(data: db.Session) -> None:
+def _genesis_row(fingerprint: str, armored: str, actor: str) -> sql.KeyAttestable:
+    _, additions = pgp.delta_fragments(frozenset(), pgp.certificate_placements(armored))
+    return sql.KeyAttestable(
+        fingerprint=fingerprint,
+        seq=1,
+        operation=sql.KeyOperation.REVISE,
+        source="web:seed",
+        additions=additions,
+        updated=datetime.datetime(2026, 1, 1, tzinfo=datetime.UTC),
+        actor=actor,
+        role=sql.KeyRole.USER,
+    )
+
+
+async def _seed_keys(data: db.Session, alpha_block: str = ALPHA_BLOCK) -> None:
     data.add(sql.Committee(key="tooling"))
-    data.add(_key(ALPHA_FINGERPRINT, "alice", "armored alpha"))
-    data.add(_key(BETA_FINGERPRINT, "bob", "armored beta"))
+    data.add(_key(ALPHA_FINGERPRINT, "alice", alpha_block))
+    data.add(_key(BETA_FINGERPRINT, "bob", BETA_BLOCK))
+    data.add(_genesis_row(ALPHA_FINGERPRINT, alpha_block, "alice"))
+    data.add(_genesis_row(BETA_FINGERPRINT, BETA_BLOCK, "bob"))
     await data.commit()
     # An artifact is attributed to a signing key rather than to the certificate carrying it
     data.add(_signing_key(ALPHA_FINGERPRINT))
diff --git a/tests/unit/test_keys_writer.py b/tests/unit/test_keys_writer.py
index 93262561..441ff14f 100644
--- a/tests/unit/test_keys_writer.py
+++ b/tests/unit/test_keys_writer.py
@@ -34,6 +34,8 @@ import atr.storage.outcome as outcome
 import atr.storage.writers.keys as keys_writer
 import tests.unit.pgp_fixtures as pgp_fixtures
 
+_ALPHA_BLOCK = pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC
+_ALPHA_FINGERPRINT = pgp_fixtures.EXPIRED_SUBKEY_PRIMARY_FINGERPRINT
 _EMBEDDED_V4_EXPIRING_KEY_ASC = """-----BEGIN PGP PUBLIC KEY BLOCK-----
 Version: GnuPG v1.4.10 (GNU/Linux)
 
@@ -136,16 +138,15 @@ def test_block_models_splits_a_two_certificate_block() -> None:
         assert key.key_model.ascii_armored_key != block
 
 
-def test_block_models_keeps_the_uploaded_text_of_a_single_certificate_block() -> None:
+def test_block_models_keeps_the_raw_bytes_of_a_single_certificate_block() -> None:
     writer, _write_as = _make_committee_member(MockData(None, committees_after_commit={}), "test")
 
-    keys = writer._CommitteeParticipant__block_models(
-        pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC, keys_writer.cache.EmailUidLookup({})
-    )
+    keys = writer._CommitteeParticipant__block_models(_ALPHA_BLOCK, keys_writer.cache.EmailUidLookup({}))
 
     assert len(keys) == 1
     assert isinstance(keys[0], keys_writer.datatypes.Key)
-    assert keys[0].key_model.ascii_armored_key == pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC
+    assert keys[0].input == pgp._dearmored(_ALPHA_BLOCK)
+    assert pgp.certificate_block_fingerprint(keys[0].key_model.ascii_armored_key) == _ALPHA_FINGERPRINT
 
 
 def test_block_models_reports_a_failing_certificate_without_losing_its_siblings() -> None:
@@ -168,34 +169,26 @@ def test_block_models_reports_a_failing_certificate_without_losing_its_siblings(
 
 
 @pytest.mark.asyncio
-async def test_database_add_model_audits_inserted_key():
-    data = MockData(None, committees_after_commit={})
-    writer, _write, write_as = _make_foundation_committer_with_audit(data)
-    insert_result = mock.MagicMock()
-    insert_result.one_or_none.return_value = object()
-    data.execute.return_value = insert_result
-    key_model = keys_writer.sql.SigningCertificate(
-        fingerprint="fp1",
-        latest_self_signature=None,
-        primary_declared_uid="Alice <al...@example.org>",
-        secondary_declared_uids=[],
-        apache_uid="alice",
-        ascii_armored_key="-----BEGIN PGP PUBLIC KEY BLOCK-----\nbody\n-----END PGP PUBLIC KEY BLOCK-----\n",
-    )
-    key = SimpleNamespace(key_model=key_model, member_ids=[])
+async def test_database_add_model_writes_a_genesis_row_and_audits_the_insert(sqlite_data):
+    writer, _write, write_as = _make_foundation_committer_with_audit(sqlite_data)
+    key = _parsed_key(_ALPHA_BLOCK)
+    key.key_model.apache_uid = "alice"
 
-    result, publications = await writer._FoundationCommitter__database_add_model(key)
+    result, publications = await writer._FoundationCommitter__database_add_model(key, "web:req-1")
 
-    assert isinstance(result, outcome.Result)
+    assert result.result_or_raise().status == datatypes.KeyStatus.INSERTED
     assert publications == {}
-    data.begin_immediate.assert_awaited_once()
-    data.commit.assert_awaited_once()
-    write_as.append_to_audit_log.assert_called_once()
+    rows = await sqlite_data.key_attestable(fingerprint=_ALPHA_FINGERPRINT).all()
+    assert [(row.seq, row.operation, row.source, row.actor, row.role) for row in rows] == [
+        (1, sql.KeyOperation.REVISE, "web:req-1", "alice", sql.KeyRole.USER)
+    ]
+    assert (rows[0].deletions, rows[0].input) == (None, pgp._dearmored(_ALPHA_BLOCK))
+    stored = await sqlite_data.signing_certificate(fingerprint=_ALPHA_FINGERPRINT).get()
+    assert stored is not None
+    assert pgp.fold_deltas([(None, rows[0].additions)]) == pgp.certificate_placements(stored.ascii_armored_key)
     audit_kwargs = write_as.append_to_audit_log.call_args.kwargs
-    assert audit_kwargs["action"] == "key_insert"
-    assert audit_kwargs["asf_uid"] == "alice"
-    assert audit_kwargs["fingerprint"] == "fp1"
-    assert audit_kwargs["key_apache_uid"] == "alice"
+    assert (audit_kwargs["action"], audit_kwargs["asf_uid"]) == ("key_insert", "alice")
+    assert audit_kwargs["fingerprint"] == _ALPHA_FINGERPRINT
 
 
 def test_deduplicate_keys_keeps_identical_copies_once() -> None:
@@ -208,56 +201,82 @@ def test_deduplicate_keys_keeps_identical_copies_once() -> None:
     assert isinstance(deduplicated[0], keys_writer.datatypes.Key)
 
 
-def test_deduplicate_keys_prefers_the_copy_which_supersedes_the_other() -> None:
+def test_deduplicate_keys_merges_the_copies_of_one_certificate() -> None:
     full = pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC
     stripped = pgp_fixtures.block_without_signature_type(full, 0x28)
+    other = _parsed_key(_ALPHA_BLOCK)
 
-    deduplicated = keys_writer._deduplicate_keys([_parsed_key(stripped), _parsed_key(full)])
-
-    assert len(deduplicated) == 1
-    assert isinstance(deduplicated[0], keys_writer.datatypes.Key)
-    assert deduplicated[0].key_model.ascii_armored_key == full
-
-
-def test_deduplicate_keys_reports_incomparable_copies_for_that_key_only() -> None:
-    full = pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC
-    unbound = pgp_fixtures.block_without_signature_type(full, 0x18)
-    other = _parsed_key(pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC)
-
-    deduplicated = keys_writer._deduplicate_keys([_parsed_key(full), other, _parsed_key(unbound)])
+    deduplicated = keys_writer._deduplicate_keys([_parsed_key(stripped), other, _parsed_key(full)])
 
     assert len(deduplicated) == 2
-    assert isinstance(deduplicated[0], ValueError)
-    assert "differing content" in str(deduplicated[0])
+    assert isinstance(deduplicated[0], keys_writer.datatypes.Key)
+    assert deduplicated[0].key_model.ascii_armored_key == pgp.merge_certificate_blocks([full])
+    assert deduplicated[0].input == pgp._dearmored(stripped) + pgp._dearmored(full)
     assert deduplicated[1] is other
 
 
 @pytest.mark.asyncio
-async def test_database_add_models_links_a_stored_key_whose_reimport_is_a_downgrade(sqlite_data):
+async def test_database_add_models_links_a_stored_key_whose_reimport_is_a_subset(sqlite_data):
     full = pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC
     fingerprint = pgp_fixtures.REVOKED_SUBKEY_PRIMARY_FINGERPRINT
     sqlite_data.add(keys_writer.sql.Committee(key="alpha"))
-    stored = _signing_certificate(fingerprint, apache_uid="alice")
-    stored.ascii_armored_key = full
-    sqlite_data.add(stored)
+    sqlite_data.add(_signing_certificate(fingerprint, apache_uid="alice", armored=full))
+    sqlite_data.add(_genesis_row(fingerprint, full, "alice"))
     await sqlite_data.commit()
     writer, _write_as = _make_committee_member(sqlite_data, "alpha")
     outcomes = outcome.List[keys_writer.datatypes.Key]()
     outcomes.append_result(_parsed_key(pgp_fixtures.block_without_signature_type(full, 0x28)))
 
     with mock.patch.object(writer, "_recheck_committee_drafts", new_callable=mock.AsyncMock):
-        outcomes, _publications = await writer._CommitteeParticipant__database_add_models(outcomes)
+        outcomes, _publications = await writer._CommitteeParticipant__database_add_models(outcomes, "web:req-1")
 
     assert outcomes.error_count == 0
+    assert outcomes.results()[0].status == datatypes.KeyStatus.LINKED
     kept = await sqlite_data.signing_certificate(fingerprint=fingerprint).get()
     assert (kept is not None) and (kept.ascii_armored_key == full)
+    rows = await sqlite_data.key_attestable(fingerprint=fingerprint).all()
+    assert [(row.seq, row.source, row.role) for row in rows] == [(1, "web:seed", sql.KeyRole.USER)]
     links = (await sqlite_data.execute(sqlmodel.select(keys_writer.sql.KeyLink))).all()
     assert [(link[0].committee_key, link[0].key_fingerprint) for link in links] == [("alpha", fingerprint)]
 
 
+@pytest.mark.asyncio
+async def test_database_add_models_stores_the_other_keys_when_one_fails(sqlite_data):
+    sqlite_data.add(keys_writer.sql.Committee(key="alpha"))
+    sqlite_data.add(_signing_certificate(_ALPHA_FINGERPRINT, apache_uid="alice", armored=_ALPHA_BLOCK))
+    await sqlite_data.commit()
+    writer, write_as = _make_committee_member(sqlite_data, "alpha")
+    outcomes = outcome.List[keys_writer.datatypes.Key]()
+    outcomes.append_result(_parsed_key(_ALPHA_BLOCK))
+    outcomes.append_result(_parsed_key(pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC))
+
+    with mock.patch.object(writer, "_recheck_committee_drafts", new_callable=mock.AsyncMock):
+        outcomes, _publications = await writer._CommitteeParticipant__database_add_models(outcomes, "web:req-1")
+
+    errors = [str(error) for error in outcomes.errors()]
+    assert (outcomes.result_count, outcomes.error_count) == (1, 1)
+    assert "has no log" in errors[0]
+    assert outcomes.results()[0].status == datatypes.KeyStatus.INSERTED | datatypes.KeyStatus.LINKED
+    assert await sqlite_data.key_attestable(fingerprint=_ALPHA_FINGERPRINT).all() == []
+    rows = await sqlite_data.key_attestable(fingerprint=pgp_fixtures.REVOKED_SUBKEY_PRIMARY_FINGERPRINT).all()
+    assert [(row.seq, row.source) for row in rows] == [(1, "web:req-1")]
+    links = (await sqlite_data.execute(sqlmodel.select(keys_writer.sql.KeyLink))).all()
+    assert [(link[0].committee_key, link[0].key_fingerprint) for link in links] == [
+        ("alpha", pgp_fixtures.REVOKED_SUBKEY_PRIMARY_FINGERPRINT)
+    ]
+    write_as.append_to_audit_log.assert_called_once()
+    audit_kwargs = write_as.append_to_audit_log.call_args.kwargs
+    assert (audit_kwargs["action"], audit_kwargs["committee_key"], audit_kwargs["linked"]) == (
+        "key_insert",
+        "alpha",
+        True,
+    )
+    assert audit_kwargs["fingerprint"] == pgp_fixtures.REVOKED_SUBKEY_PRIMARY_FINGERPRINT
+
+
 @pytest.mark.asyncio
 async def test_delete_committee_keys_audits_committed_delete_when_sync_fails(sqlite_data):
-    await _seed_committee_key(sqlite_data, "alpha", "fp1")
+    await _seed_committee_key(sqlite_data, "alpha", _ALPHA_FINGERPRINT, _ALPHA_BLOCK)
     writer, write_as = _make_foundation_admin(sqlite_data, "alpha")
     error_message = "Failed to remove KEYS file for committee alpha: permission denied"
 
@@ -267,7 +286,7 @@ async def test_delete_committee_keys_audits_committed_delete_when_sync_fails(sql
         new=mock.AsyncMock(side_effect=storage.AccessError(error_message)),
     ):
         with pytest.raises(storage.AccessError, match="permission denied"):
-            await writer.delete_committee_keys()
+            await writer.delete_committee_keys("web:req-1")
 
     assert write_as.append_to_audit_log.call_count == 2
 
@@ -277,19 +296,19 @@ async def test_delete_committee_keys_audits_committed_delete_when_sync_fails(sql
     assert delete_audit["committee_key"] == "alpha"
     assert delete_audit["keys_unlinked"] == 1
     assert delete_audit["keys_deleted"] == 1
-    assert delete_audit["fingerprints"] == ["fp1"]
+    assert delete_audit["fingerprints"] == [_ALPHA_FINGERPRINT]
 
     assert sync_failure_audit["action"] == "delete_committee_keys_sync_failed"
     assert sync_failure_audit["committee_key"] == "alpha"
     assert sync_failure_audit["error"] == error_message
 
-    key = await sqlite_data.signing_certificate(fingerprint="fp1", deleted=True).get()
+    key = await sqlite_data.signing_certificate(fingerprint=_ALPHA_FINGERPRINT, deleted=True).get()
     assert key is not None
 
 
 @pytest.mark.asyncio
 async def test_delete_committee_keys_removes_links_and_orphaned_keys(sqlite_data):
-    await _seed_committee_key(sqlite_data, "alpha", "fp1")
+    await _seed_committee_key(sqlite_data, "alpha", _ALPHA_FINGERPRINT, _ALPHA_BLOCK)
     sqlite_data.add(keys_writer.sql.Committee(key="beta"))
     sqlite_data.add(_signing_certificate("fp2", apache_uid="bob"))
     await sqlite_data.commit()
@@ -300,12 +319,18 @@ async def test_delete_committee_keys_removes_links_and_orphaned_keys(sqlite_data
 
     with mock.patch.object(writer, "_sync_committee_keys_file", new_callable=mock.AsyncMock) as mock_sync:
         mock_sync.return_value = (0, outcome.Result(keys_writer.datatypes.KeysPublish.SVN_NOT_CONFIGURED))
-        num_unlinked, num_deleted, _ = await writer.delete_committee_keys()
+        num_unlinked, num_deleted, _ = await writer.delete_committee_keys("web:req-1")
 
     assert num_unlinked == 2
     assert num_deleted == 1
-    orphaned = await sqlite_data.signing_certificate(fingerprint="fp1", deleted=True).get()
+    orphaned = await sqlite_data.signing_certificate(fingerprint=_ALPHA_FINGERPRINT, deleted=True).get()
     assert orphaned is not None
+    rows = await sqlite_data.key_attestable(fingerprint=_ALPHA_FINGERPRINT).all()
+    assert [(row.seq, row.operation, row.source, row.role) for row in rows] == [
+        (1, sql.KeyOperation.REVISE, "web:seed", sql.KeyRole.USER),
+        (2, sql.KeyOperation.DELETE, "web:req-1", sql.KeyRole.USER),
+    ]
+    assert (rows[1].deletions, rows[1].additions) == (None, None)
     shared = await sqlite_data.signing_certificate(fingerprint="fp2").get()
     assert shared is not None
     assert shared.deleted is None
@@ -318,7 +343,7 @@ async def test_delete_committee_keys_removes_links_and_orphaned_keys(sqlite_data
     assert audit_kwargs["committee_key"] == "alpha"
     assert audit_kwargs["keys_unlinked"] == 2
     assert audit_kwargs["keys_deleted"] == 1
-    assert set(audit_kwargs["fingerprints"]) == {"fp1", "fp2"}
+    assert set(audit_kwargs["fingerprints"]) == {_ALPHA_FINGERPRINT, "fp2"}
 
 
 @pytest.mark.asyncio
@@ -327,7 +352,7 @@ async def test_delete_committee_keys_returns_zero_when_no_keys(sqlite_data):
     await sqlite_data.commit()
     writer, write_as = _make_foundation_admin(sqlite_data, "alpha")
 
-    num_unlinked, num_deleted, _ = await writer.delete_committee_keys()
+    num_unlinked, num_deleted, _ = await writer.delete_committee_keys("web:req-1")
 
     assert num_unlinked == 0
     assert num_deleted == 0
@@ -336,7 +361,10 @@ async def test_delete_committee_keys_returns_zero_when_no_keys(sqlite_data):
 
 @pytest.mark.asyncio
 async def test_dissociate_fingerprints_removes_links_and_keeps_certificates(sqlite_data):
-    await _seed_committee_key(sqlite_data, "alpha", "fp1")
+    sqlite_data.add(keys_writer.sql.Committee(key="alpha"))
+    sqlite_data.add(_signing_certificate("fp1", apache_uid="alice"))
+    await sqlite_data.commit()
+    sqlite_data.add(keys_writer.sql.KeyLink(committee_key="alpha", key_fingerprint="fp1"))
     sqlite_data.add(keys_writer.sql.Committee(key="beta"))
     sqlite_data.add(_signing_certificate("fp2", apache_uid="bob"))
     await sqlite_data.commit()
@@ -367,32 +395,24 @@ async def test_dissociate_fingerprints_removes_links_and_keeps_certificates(sqli
 
 
 @pytest.mark.asyncio
-async def test_delete_key_removal_publishes_empty_keys_file():
-    owned_key = SimpleNamespace(
-        fingerprint="fp1",
-        committees=[SimpleNamespace(key="alpha")],
-    )
-    data = MockData(
-        owned_key,
-        committees_after_commit={"alpha": _committee("alpha", [])},
-    )
-    data.execute.return_value = SimpleNamespace(rowcount=1)
-    writer, _write, write_as = _make_foundation_committer_with_audit(data)
+async def test_delete_key_removal_publishes_empty_keys_file(sqlite_data):
+    await _seed_committee_key(sqlite_data, "alpha", _ALPHA_FINGERPRINT, _ALPHA_BLOCK)
+    writer, _write, write_as = _make_foundation_committer_with_audit(sqlite_data)
 
     publish = mock.AsyncMock(return_value=outcome.Result(datatypes.KeysPublish.PUBLISHED))
     with mock.patch.object(writer, "_publish_keys_to_svn", publish):
-        result = await writer.delete_key("fp1")
+        result = await writer.delete_key(_ALPHA_FINGERPRINT, "web:req-1")
 
     assert isinstance(result, outcome.Result)
     publish.assert_awaited_once()
     assert publish.await_args.args[1] is None
-    data.delete.assert_not_awaited()
-    data.commit.assert_awaited_once()
     write_as.append_to_audit_log.assert_called_once()
     audit_kwargs = write_as.append_to_audit_log.call_args.kwargs
     assert audit_kwargs["action"] == "key_delete"
-    assert audit_kwargs["fingerprint"] == "fp1"
+    assert audit_kwargs["fingerprint"] == _ALPHA_FINGERPRINT
     assert audit_kwargs["committee_keys"] == ["alpha"]
+    rows = await sqlite_data.key_attestable(fingerprint=_ALPHA_FINGERPRINT).all()
+    assert [(row.seq, row.operation) for row in rows] == [(1, sql.KeyOperation.REVISE), (2, sql.KeyOperation.DELETE)]
 
 
 @pytest.mark.asyncio
@@ -418,7 +438,7 @@ async def test_ensure_allows_key_without_apache_uid_for_bulk_import() -> None:
             new=mock.AsyncMock(return_value=(database_outcomes, {})),
         ) as database_add_models,
     ):
-        result, publications = await writer._CommitteeParticipant__ensure("keys text")
+        result, publications = await writer._CommitteeParticipant__ensure("keys text", "web:req-1")
 
     assert result is database_outcomes
     assert publications == {}
@@ -450,7 +470,7 @@ async def test_ensure_stored_one_accepts_key_with_apache_uid() -> None:
             new=mock.AsyncMock(return_value=(database_outcome, {})),
         ) as database_add_model,
     ):
-        result, publications = await writer.ensure_stored_one("keys text")
+        result, publications = await writer.ensure_stored_one("keys text", "web:req-1")
 
     assert result is database_outcome
     assert publications == {}
@@ -459,7 +479,7 @@ async def test_ensure_stored_one_accepts_key_with_apache_uid() -> None:
     assert block_model_create.call_args.args[0] == "block-one"
     assert isinstance(block_model_create.call_args.args[1], keys_writer.cache.EmailUidLookup)
     block_model.assert_not_called()
-    database_add_model.assert_awaited_once_with(key)
+    database_add_model.assert_awaited_once_with(key, "web:req-1")
 
 
 @pytest.mark.asyncio
@@ -478,10 +498,10 @@ async def test_ensure_stored_one_accepts_test_key_without_email_cache() -> None:
         mock.patch.object(
             writer,
             "_FoundationCommitter__database_add_model",
-            new=mock.AsyncMock(side_effect=lambda key: (outcome.Result(key), {})),
+            new=mock.AsyncMock(side_effect=lambda key, _source: (outcome.Result(key), {})),
         ) as database_add_model,
     ):
-        result, _publications = await writer.ensure_stored_one(key_text)
+        result, _publications = await writer.ensure_stored_one(key_text, "web:req-1")
 
     key = result.result_or_raise()
     assert key.key_model.apache_uid == "test"
@@ -498,7 +518,7 @@ async def test_ensure_stored_one_rejects_a_block_with_two_keys() -> None:
         pgp_fixtures.EXPIRED_SUBKEY_PUBLIC_KEY_ASC, pgp_fixtures.REVOKED_SUBKEY_PUBLIC_KEY_ASC
     )
 
-    result, publications = await writer.ensure_stored_one(block)
+    result, publications = await writer.ensure_stored_one(block, "web:req-1")
 
     assert publications == {}
     assert "contains 2 keys" in str(result.error_or_none())
@@ -528,7 +548,7 @@ async def test_ensure_stored_one_rejects_key_without_apache_uid() -> None:
             new=mock.AsyncMock(),
         ) as database_add_model,
     ):
-        result, publications = await writer.ensure_stored_one("keys text")
+        result, publications = await writer.ensure_stored_one("keys text", "web:req-1")
 
     assert isinstance(result, outcome.Error)
     assert publications == {}
@@ -569,7 +589,7 @@ async def test_ensure_uses_cached_email_lookup_for_bulk_import() -> None:
             new=mock.AsyncMock(return_value=(database_outcomes, {})),
         ) as database_add_models,
     ):
-        result, _publications = await writer._CommitteeParticipant__ensure("keys text")
+        result, _publications = await writer._CommitteeParticipant__ensure("keys text", "web:req-1")
 
     assert result is database_outcomes
     email_uid_view.assert_awaited_once()
@@ -849,9 +869,10 @@ def _make_foundation_committer_with_audit(data: MockData, asf_uid: str = "alice"
 
 def _parsed_key(armored: str) -> keys_writer.datatypes.Key:
     key, _ = keys_writer.openpgp.composed.SignedPublicKey.from_armor(armored)
-    model = _signing_certificate(key.fingerprint.lower(), apache_uid=None)
-    model.ascii_armored_key = armored
-    return keys_writer.datatypes.Key(status=keys_writer.datatypes.KeyStatus.PARSED, key_model=model)
+    model = _signing_certificate(key.fingerprint.lower(), apache_uid=None, armored=armored)
+    return keys_writer.datatypes.Key(
+        status=keys_writer.datatypes.KeyStatus.PARSED, key_model=model, input=pgp._dearmored(armored)
+    )
 
 
 def _playwright_test_key_text() -> str:
@@ -878,20 +899,37 @@ def _public_key(
     )
 
 
-async def _seed_committee_key(data: db.Session, committee_key: str, fingerprint: str) -> None:
+def _genesis_row(fingerprint: str, armored: str, actor: str) -> sql.KeyAttestable:
+    _, additions = pgp.delta_fragments(frozenset(), pgp.certificate_placements(armored))
+    return sql.KeyAttestable(
+        fingerprint=fingerprint,
+        seq=1,
+        operation=sql.KeyOperation.REVISE,
+        source="web:seed",
+        additions=additions,
+        updated=datetime.datetime(2026, 1, 1, tzinfo=datetime.UTC),
+        actor=actor,
+        role=sql.KeyRole.USER,
+    )
+
+
+async def _seed_committee_key(data: db.Session, committee_key: str, fingerprint: str, armored: str) -> None:
     data.add(keys_writer.sql.Committee(key=committee_key))
-    data.add(_signing_certificate(fingerprint, apache_uid="alice"))
+    data.add(_signing_certificate(fingerprint, apache_uid="alice", armored=armored))
+    data.add(_genesis_row(fingerprint, armored, "alice"))
     await data.commit()
     data.add(keys_writer.sql.KeyLink(committee_key=committee_key, key_fingerprint=fingerprint))
     await data.commit()
 
 
-def _signing_certificate(fingerprint: str, apache_uid: str | None) -> keys_writer.sql.SigningCertificate:
+def _signing_certificate(
+    fingerprint: str, apache_uid: str | None, armored: str | None = None
+) -> keys_writer.sql.SigningCertificate:
     return keys_writer.sql.SigningCertificate(
         fingerprint=fingerprint,
         latest_self_signature=None,
         primary_declared_uid="Alice <al...@example.org>",
         secondary_declared_uids=[],
         apache_uid=apache_uid,
-        ascii_armored_key="-----BEGIN PGP PUBLIC KEY BLOCK-----\nbody\n-----END PGP PUBLIC KEY BLOCK-----\n",
+        ascii_armored_key=armored or "-----BEGIN PGP PUBLIC KEY BLOCK-----\nbody\n-----END PGP PUBLIC KEY BLOCK-----\n",
     )


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@tooling.apache.org
For additional commands, e-mail: commits-help@tooling.apache.org