208 lines
7.9 KiB
Python
208 lines
7.9 KiB
Python
# Copyright 2026 Umbra Tools (umbratools.com)
|
|
# SPDX-License-Identifier: Apache-2.0
|
|
|
|
"""Content-addressed source-unit identity (``source_unit_key``).
|
|
|
|
Why this exists
|
|
---------------
|
|
Utterances are currently identified by ``utterance_index``, which is *position*.
|
|
Re-segmentation renumbers a chapter, and ``DELETE /api/utterance/{id}`` renumbers the
|
|
rest of the chapter and renames files on disk. ``specs/18-UmbraTrans.md`` §3.3 forbids
|
|
mutable indices as durable cross-language IDs, and ``specs/20`` §E.1 requires a
|
|
content-addressed key recorded at generation time with filenames unchanged.
|
|
|
|
The key
|
|
-------
|
|
``uk1`` version, byte order, hex-encoded SHA-256::
|
|
|
|
sha256(b"uk1\\x00" + chapter_text_sha256 + b"\\x00" + occurrence + b"\\x00" + text_sha256)
|
|
|
|
* ``chapter_text_sha256`` - SHA-256 of ``chapters.source_text``. This substitutes for a
|
|
source-revision id: it is self-verifying, so no revision table is needed, and any edit to
|
|
the chapter invalidates exactly the units whose text changed.
|
|
* ``occurrence`` - 0-based count of *earlier* utterances in the same chapter with an
|
|
identical text hash, in document order. Disambiguates repeated lines ("Yes.") without
|
|
depending on the index itself.
|
|
* ``text_sha256`` - SHA-256 of the utterance text, exact.
|
|
|
|
Properties this deliberately has
|
|
---------------------------------
|
|
* **No index in the key.** Renumbering cannot change it.
|
|
* **Index-independent resolution.** Given a key, the row is found by primary key lookup;
|
|
``utterance_index`` is never needed (controller validation phase 3).
|
|
* **Per-project agnostic.** Two projects with the same chapter text and the same utterance
|
|
text produce the same key. That is intentional (content identity), and it means the
|
|
controller must salt per tenant at ingest if isolation requires it - flagged as a finding
|
|
rather than silently namespaced here.
|
|
* **Idempotent.** Recomputing over an unchanged chapter reproduces every key exactly.
|
|
|
|
This is additive. It adds one nullable column, changes no filename, and does not participate
|
|
in ``is_current`` staleness decisions.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import hashlib
|
|
from typing import TYPE_CHECKING, Any
|
|
|
|
if TYPE_CHECKING:
|
|
import sqlite3
|
|
from collections.abc import Iterable, Sequence
|
|
|
|
# Bump if the derivation ever changes; existing keys stay valid and readable.
|
|
KEY_VERSION = "uk1"
|
|
|
|
COLUMN = "source_unit_key"
|
|
|
|
|
|
def _sha256(data: bytes) -> str:
|
|
return hashlib.sha256(data).hexdigest()
|
|
|
|
|
|
def chapter_text_sha256(source_text: str | None) -> str:
|
|
"""Hash a chapter's source text. ``None``/empty still hashes (deterministically)."""
|
|
return _sha256((source_text or "").encode("utf-8"))
|
|
|
|
|
|
def text_sha256(text: str | None) -> str:
|
|
"""Hash an utterance's exact text."""
|
|
return _sha256((text or "").encode("utf-8"))
|
|
|
|
|
|
def compute_source_unit_key(chapter_sha: str, text: str, occurrence: int) -> str:
|
|
"""Derive one key. ``occurrence`` disambiguates repeated text within a chapter."""
|
|
payload = b"\x00".join(
|
|
(
|
|
KEY_VERSION.encode("ascii"),
|
|
chapter_sha.encode("ascii"),
|
|
str(occurrence).encode("ascii"),
|
|
text_sha256(text).encode("ascii"),
|
|
)
|
|
)
|
|
return _sha256(payload)
|
|
|
|
|
|
def assign_keys_for_chapter(chapter_sha: str, texts: Sequence[str]) -> list[str]:
|
|
"""Return the key for every utterance of one chapter, in document order."""
|
|
seen: dict[str, int] = {}
|
|
keys: list[str] = []
|
|
for text in texts:
|
|
t_sha = text_sha256(text)
|
|
occurrence = seen.get(t_sha, 0)
|
|
seen[t_sha] = occurrence + 1
|
|
keys.append(compute_source_unit_key(chapter_sha, text, occurrence))
|
|
return keys
|
|
|
|
|
|
def ensure_column(conn: sqlite3.Connection) -> bool:
|
|
"""Add ``source_unit_key`` if absent. Returns True when it added the column."""
|
|
cols = {row[1] for row in conn.execute("PRAGMA table_info(utterances)")}
|
|
if COLUMN in cols:
|
|
return False
|
|
conn.execute(f"ALTER TABLE utterances ADD COLUMN {COLUMN} TEXT")
|
|
conn.execute(f"CREATE INDEX IF NOT EXISTS idx_utterances_source_unit ON utterances({COLUMN})")
|
|
conn.commit()
|
|
return True
|
|
|
|
|
|
def _chapter_order_sql(conn: sqlite3.Connection) -> str:
|
|
"""Order chapters by display order, tolerating a missing ``chapter_index``.
|
|
|
|
Two of the four live projects carry an older schema shape (see
|
|
``manifest.md`` §1.3), so this must not assume the column exists.
|
|
"""
|
|
cols = {row[1] for row in conn.execute("PRAGMA table_info(chapters)")}
|
|
order = "chapter_index, id" if "chapter_index" in cols else "id"
|
|
return f"SELECT id FROM chapters ORDER BY {order}"
|
|
|
|
|
|
def chapter_keys(conn: sqlite3.Connection, chapter_id: int) -> list[tuple[int, str]]:
|
|
"""Compute ``(utterance_id, key)`` for one chapter without writing anything."""
|
|
chapter = conn.execute(
|
|
"SELECT source_text FROM chapters WHERE id = ?", (chapter_id,)
|
|
).fetchone()
|
|
chapter_sha = chapter_text_sha256(chapter[0] if chapter else None)
|
|
rows = conn.execute(
|
|
"SELECT id, text FROM utterances WHERE chapter_id = ? ORDER BY utterance_index",
|
|
(chapter_id,),
|
|
).fetchall()
|
|
keys = assign_keys_for_chapter(chapter_sha, [r[1] or "" for r in rows])
|
|
return [(row[0], key) for row, key in zip(rows, keys, strict=True)]
|
|
|
|
|
|
def stamp_chapter(conn: sqlite3.Connection, chapter_id: int) -> int:
|
|
"""Write keys for one chapter. Returns how many rows changed."""
|
|
ensure_column(conn)
|
|
pairs = chapter_keys(conn, chapter_id)
|
|
changed = 0
|
|
for utterance_id, key in pairs:
|
|
cursor = conn.execute(
|
|
f"UPDATE utterances SET {COLUMN} = ? "
|
|
f"WHERE id = ? AND ({COLUMN} IS NULL OR {COLUMN} != ?)",
|
|
(key, utterance_id, key),
|
|
)
|
|
changed += cursor.rowcount if cursor.rowcount and cursor.rowcount > 0 else 0
|
|
conn.commit()
|
|
return changed
|
|
|
|
|
|
def ensure_chapter_keys(conn: sqlite3.Connection, chapter_id: int) -> int:
|
|
"""Fill only the keys that are missing for one chapter. Cheap, idempotent, safe.
|
|
|
|
Called at generation time so every path that produces audio also produces identity,
|
|
including paths this file knows nothing about.
|
|
"""
|
|
ensure_column(conn)
|
|
missing = conn.execute(
|
|
f"SELECT COUNT(*) FROM utterances WHERE chapter_id = ? AND {COLUMN} IS NULL",
|
|
(chapter_id,),
|
|
).fetchone()[0]
|
|
if not missing:
|
|
return 0
|
|
return stamp_chapter(conn, chapter_id)
|
|
|
|
|
|
def backfill(
|
|
conn: sqlite3.Connection, chapter_ids: Iterable[int] | None = None
|
|
) -> dict[str, int]:
|
|
"""Backfill every chapter (or a subset). Returns counts for the report."""
|
|
if chapter_ids is None:
|
|
ids = [row[0] for row in conn.execute(_chapter_order_sql(conn))]
|
|
else:
|
|
ids = list(chapter_ids)
|
|
ensure_column(conn)
|
|
stamped = 0
|
|
for chapter_id in ids:
|
|
stamped += stamp_chapter(conn, chapter_id)
|
|
total, filled = conn.execute(
|
|
f"SELECT COUNT(*), SUM(CASE WHEN {COLUMN} IS NOT NULL THEN 1 ELSE 0 END) FROM utterances"
|
|
).fetchone()
|
|
return {
|
|
"chapters": len(ids),
|
|
"rows_stamped": stamped,
|
|
"utterances_total": total or 0,
|
|
"utterances_with_key": filled or 0,
|
|
"utterances_without_key": (total or 0) - (filled or 0),
|
|
}
|
|
|
|
|
|
def key_report(conn: sqlite3.Connection) -> dict[str, Any]:
|
|
"""Read-only coverage report - evidence for controller validation phase 2/3."""
|
|
has_column = COLUMN in {
|
|
row[1] for row in conn.execute("PRAGMA table_info(utterances)")
|
|
}
|
|
if not has_column:
|
|
return {"column_present": False}
|
|
total, filled, distinct = conn.execute(
|
|
f"SELECT COUNT(*), SUM(CASE WHEN {COLUMN} IS NOT NULL THEN 1 ELSE 0 END), "
|
|
f"COUNT(DISTINCT {COLUMN}) FROM utterances"
|
|
).fetchone()
|
|
return {
|
|
"column_present": True,
|
|
"key_version": KEY_VERSION,
|
|
"utterances_total": total or 0,
|
|
"utterances_with_key": filled or 0,
|
|
"distinct_keys": distinct or 0,
|
|
"collisions": (filled or 0) - (distinct or 0),
|
|
}
|