Add catalog.py for manually adding authors and series to the database.

.github/workflows/pytest.yml

@@ -1,7 +1,6 @@
 name: pytest
 
 on:
-  workflow_dispatch:
   push:
     branches:
       - main

.github/workflows/update-flake-lock.yml

@@ -8,7 +8,6 @@ jobs:
   lockfile:
     runs-on: self-hosted
     permissions:
-      actions: write
       contents: write
       pull-requests: write
     steps:

flake.lock

@@ -8,11 +8,11 @@
       },
       "locked": {
         "dir": "pkgs/firefox-addons",
-        "lastModified": 1781270820,
+        "lastModified": 1780027372,
-        "narHash": "sha256-KuDdN/p4UtqUb1wnjo8a/YougmRXEaKvUoGHkYnuAq0=",
+        "narHash": "sha256-LQ3CUdVZoKQqWzS2eEpY0rp9bJuzqydNFJUiJ6De9r8=",
         "owner": "rycee",
         "repo": "nur-expressions",
-        "rev": "5f4259c0c832a93e13c5ec481d3318ce1394a8f9",
+        "rev": "ef18b76eabdf4f9b2ce8e99e78ce698923693300",
         "type": "gitlab"
       },
       "original": {
@@ -29,11 +29,11 @@
         ]
       },
       "locked": {
-        "lastModified": 1781305496,
+        "lastModified": 1780099287,
-        "narHash": "sha256-g8Vv4Qfc7n+lgov97REu3X6BeJtvYY0hlSUZR1GrGQQ=",
+        "narHash": "sha256-efIPwVGtIWIjWcznhaop6XN6HxnOL8800hF6CBNvlqQ=",
         "owner": "nix-community",
         "repo": "home-manager",
-        "rev": "c87a39aa979acc4848016d2220c6238390d84779",
+        "rev": "7d8127d308c3fb9664f7e643eec944be74ebb37d",
         "type": "github"
       },
       "original": {
@@ -47,11 +47,11 @@
         "nixpkgs": "nixpkgs"
       },
       "locked": {
-        "lastModified": 1781168557,
+        "lastModified": 1780065812,
-        "narHash": "sha256-LOnLQ2tpYF9gqIDDr3+j3DbpJJr/QCH6zPRT2GzEUOE=",
+        "narHash": "sha256-SCSLUKBmwlSLGQ8Xbr8PjRFtiHNk0l9ktqkcmqdBkfE=",
         "owner": "nixos",
         "repo": "nixos-hardware",
-        "rev": "6358ff76821101c178e3ab4919a62799bfe3652e",
+        "rev": "b76b5639c0593e0aeb0b5879ad62d4b30596c144",
         "type": "github"
       },
       "original": {
@@ -76,11 +76,11 @@
     },
     "nixpkgs-master": {
       "locked": {
-        "lastModified": 1781306963,
+        "lastModified": 1780101106,
-        "narHash": "sha256-qWZ+XEwsf8pN4DnYyyLbmAyj1a74gOO/VNCff44MuB4=",
+        "narHash": "sha256-VcvUdRb9rzKBbF6oMaMiAt+6HZQ1gom9b2dUybhVTVY=",
         "owner": "nixos",
         "repo": "nixpkgs",
-        "rev": "1eee1a273e2e3fdc3db5b1a4a66f06cd5749db69",
+        "rev": "26b82d423c4f6fda4a8015182516c938f8104337",
         "type": "github"
       },
       "original": {
@@ -108,11 +108,11 @@
     },
     "nixpkgs_2": {
       "locked": {
-        "lastModified": 1781074563,
+        "lastModified": 1779560665,
-        "narHash": "sha256-md8WlXOlfnIeHeOScMTTHFyf2d6iaTwPl2apR5EQ3P4=",
+        "narHash": "sha256-tpyBcxPpcQb8ukyNF7DoCwfSY3VPsxHoYwj00Cayv5o=",
         "owner": "nixos",
         "repo": "nixpkgs",
-        "rev": "9ae611a455b90cf061d8f332b977e387bda8e1ca",
+        "rev": "64c08a7ca051951c8eae34e3e3cb1e202fe36786",
         "type": "github"
       },
       "original": {
@@ -141,11 +141,11 @@
         ]
       },
       "locked": {
-        "lastModified": 1780547341,
+        "lastModified": 1777944972,
-        "narHash": "sha256-Gq8KNx5A7hBB3uGJaj6eQfLDIz5YdLu92gqBcvHvoUo=",
+        "narHash": "sha256-VfGRo1qTBKOe3s2gOv8LSoA6Fk19PvBlwQ1ECN0Evn8=",
         "owner": "Mic92",
         "repo": "sops-nix",
-        "rev": "9ed65852b6257fbeae4355bc24ecfea307ca759a",
+        "rev": "c591bf665727040c6cc5cb409079acb22dcce33c",
         "type": "github"
       },
       "original": {

python/alembic/richie/versions/2026_06_03-adding_audiobook_libreary_metadata_d7864d1ffc17.py

@@ -0,0 +1,93 @@
+"""adding audiobook libreary metadata.
+
+Revision ID: d7864d1ffc17
+Revises: c8a794340928
+Create Date: 2026-06-03 20:24:09.200837
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+import sqlalchemy as sa
+from alembic import op
+
+from python.orm import RichieBase
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "d7864d1ffc17"
+down_revision: str | None = "c8a794340928"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+schema = RichieBase.schema_name
+
+
+def upgrade() -> None:
+    """Upgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "audiobook_author",
+        sa.Column("name", sa.String(), nullable=False),
+        sa.Column("id", sa.Integer(), nullable=False),
+        sa.Column("created", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.Column("updated", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_audiobook_author")),
+        sa.UniqueConstraint("name", name=op.f("uq_audiobook_author_name")),
+        schema=schema,
+    )
+    op.create_table(
+        "audiobook_series",
+        sa.Column("name", sa.String(), nullable=False),
+        sa.Column("author_id", sa.Integer(), nullable=False),
+        sa.Column("id", sa.Integer(), nullable=False),
+        sa.Column("created", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.Column("updated", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["author_id"],
+            [f"{schema}.audiobook_author.id"],
+            name=op.f("fk_audiobook_series_author_id_audiobook_author"),
+            ondelete="CASCADE",
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_audiobook_series")),
+        sa.UniqueConstraint("author_id", "name", name=op.f("uq_audiobook_series_author_id")),
+        schema=schema,
+    )
+    op.create_table(
+        "audiobook",
+        sa.Column("title", sa.String(), nullable=False),
+        sa.Column("author_id", sa.Integer(), nullable=False),
+        sa.Column("series_id", sa.Integer(), nullable=True),
+        sa.Column("series_index", sa.Integer(), nullable=False),
+        sa.Column("id", sa.Integer(), nullable=False),
+        sa.Column("created", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.Column("updated", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["author_id"],
+            [f"{schema}.audiobook_author.id"],
+            name=op.f("fk_audiobook_author_id_audiobook_author"),
+            ondelete="CASCADE",
+        ),
+        sa.ForeignKeyConstraint(
+            ["series_id"],
+            [f"{schema}.audiobook_series.id"],
+            name=op.f("fk_audiobook_series_id_audiobook_series"),
+            ondelete="SET NULL",
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_audiobook")),
+        schema=schema,
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Downgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("audiobook", schema=schema)
+    op.drop_table("audiobook_series", schema=schema)
+    op.drop_table("audiobook_author", schema=schema)
+    # ### end Alembic commands ###

python/gitea.py

@@ -4,12 +4,10 @@ from __future__ import annotations
 
 from dataclasses import dataclass
 from typing import Self
-from urllib.parse import quote
 
 import httpx
 
 DEFAULT_PAGE_SIZE = 100
-EXPECTED_NO_CONTENT = 204
 EXPECTED_CREATED = 201
 EXPECTED_OK = 200
 
@@ -224,16 +222,6 @@ class GiteaClient:
             json=payload,
         )
 
-    def dispatch_workflow(self, *, owner: str, repo: str, workflow_id: str, ref: str) -> None:
-        """Trigger a workflow_dispatch run."""
-        workflow_path = quote(workflow_id, safe="")
-        self._request(
-            "POST",
-            f"/api/v1/repos/{owner}/{repo}/actions/workflows/{workflow_path}/dispatches",
-            expected_statuses={EXPECTED_OK, EXPECTED_NO_CONTENT},
-            json={"ref": ref},
-        )
-
     def list_run_jobs(self, *, owner: str, repo: str, run_id: str | int) -> list[WorkflowJob]:
         """List workflow jobs for a specific run."""
         jobs: list[WorkflowJob] = []

python/gitea_flake_lock.py

@@ -14,7 +14,6 @@ DEFAULT_BASE_BRANCH = "main"
 DEFAULT_BRANCH = "automation/update-flake-lock"
 DEFAULT_GITEA_URL = "https://gitea.tmmworkshop.com"
 PR_LABELS = ["dependencies", "automated", "flake_lock_update"]
-PR_CHECK_WORKFLOWS = ["build_systems.yml", "treefmt.yml", "pytest.yml"]
 PR_TITLE = "Update flake.lock"
 PR_BODY = "Automated flake.lock update."
 
@@ -58,12 +57,6 @@ def find_flake_lock_pull_request(client: GiteaClient, *, owner: str, repo: str)
     return pull_requests[0]
 
 
-def dispatch_pull_request_checks(client: GiteaClient, *, owner: str, repo: str, branch: str) -> None:
-    """Dispatch the workflows that normally run for pull requests."""
-    for workflow in PR_CHECK_WORKFLOWS:
-        client.dispatch_workflow(owner=owner, repo=repo, workflow_id=workflow, ref=branch)
-
-
 def has_worktree_changes() -> bool:
     """Return whether `flake.lock` has worktree changes."""
     result = run_cmd(["git", "diff", "--quiet", "--", "flake.lock"], check=False)
@@ -120,9 +113,6 @@ def update(
             branch=branch,
             base=base,
         )
-        # We can remove this if Gitea fixes the following issue:
-        # https://github.com/go-gitea/gitea/issues/33963
-        dispatch_pull_request_checks(client, owner=owner, repo=repo_name, branch=branch)
     typer.echo(pull_request.html_url or f"Pull request #{pull_request.number}")
 
 

python/orm/richie/__init__.py

@@ -2,6 +2,7 @@
 
 from __future__ import annotations
 
+from python.orm.richie.audiobook import Audiobook, AudiobookAuthor, AudiobookSeries
 from python.orm.richie.base import RichieBase, TableBase, TableBaseBig, TableBaseSmall
 from python.orm.richie.contact import (
     Contact,
@@ -12,6 +13,9 @@ from python.orm.richie.contact import (
 )
 
 __all__ = [
+    "Audiobook",
+    "AudiobookAuthor",
+    "AudiobookSeries",
     "Contact",
     "ContactNeed",
     "ContactRelationship",

python/orm/richie/audiobook.py

@@ -0,0 +1,46 @@
+"""Audiobook catalog models."""
+
+from __future__ import annotations
+
+from sqlalchemy import ForeignKey, String, UniqueConstraint
+from sqlalchemy.orm import Mapped, mapped_column, relationship
+
+from python.orm.richie.base import TableBase
+
+
+class AudiobookAuthor(TableBase):
+    """Canonical audiobook author."""
+
+    __tablename__ = "audiobook_author"
+
+    name: Mapped[str] = mapped_column(String, unique=True)
+
+    books: Mapped[list[Audiobook]] = relationship("Audiobook", back_populates="author")
+    series: Mapped[list[AudiobookSeries]] = relationship("AudiobookSeries", back_populates="author")
+
+
+class AudiobookSeries(TableBase):
+    """Canonical audiobook series."""
+
+    __tablename__ = "audiobook_series"
+    __table_args__ = (UniqueConstraint("author_id", "name"),)
+
+    name: Mapped[str] = mapped_column(String)
+    author_id: Mapped[int] = mapped_column(ForeignKey("main.audiobook_author.id", ondelete="CASCADE"))
+
+    author: Mapped[AudiobookAuthor] = relationship("AudiobookAuthor", back_populates="series")
+    books: Mapped[list[Audiobook]] = relationship("Audiobook", back_populates="series")
+
+
+class Audiobook(TableBase):
+    """Canonical audiobook title."""
+
+    __tablename__ = "audiobook"
+
+    title: Mapped[str] = mapped_column(String)
+    author_id: Mapped[int] = mapped_column(ForeignKey("main.audiobook_author.id", ondelete="CASCADE"))
+    series_id: Mapped[int | None] = mapped_column(ForeignKey("main.audiobook_series.id", ondelete="SET NULL"))
+    series_index: Mapped[int] = mapped_column(default=0)
+
+    author: Mapped[AudiobookAuthor] = relationship("AudiobookAuthor", back_populates="books")
+    series: Mapped[AudiobookSeries | None] = relationship("AudiobookSeries", back_populates="books")

python/tools/audiobook/catalog.py

@@ -0,0 +1,176 @@
+"""Import audiobook catalog authors and series from CSV files."""
+
+from __future__ import annotations
+
+import csv
+import logging
+from pathlib import Path  # noqa: TC003 This is required for the typer CLI
+from typing import Annotated
+
+import typer
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from python.common import configure_logger
+from python.orm.common import get_postgres_engine
+from python.orm.richie import AudiobookAuthor, AudiobookSeries
+
+logger = logging.getLogger(__name__)
+
+AUTHOR_NAME_COLUMN = "author_name"
+ID_COLUMN = "id"
+NAME_COLUMN = "name"
+
+
+class CatalogImportError(ValueError):
+    """CSV catalog import failed validation."""
+
+
+def main(
+    authors_csv: Annotated[Path, typer.Argument(help="CSV with name and optional id.")],
+    series_csv: Annotated[Path, typer.Argument(help="CSV with name, author_name, and optional id.")],
+) -> None:
+    """Upsert audiobook authors and series from CSV files."""
+    configure_logger()
+    try:
+        engine = get_postgres_engine(name="RICHIE")
+        with Session(engine) as session:
+            author_count = upsert_authors_from_csv(session, authors_csv)
+            series_count = upsert_series_from_csv(session, series_csv)
+            session.commit()
+    except CatalogImportError as error:
+        typer.echo(str(error), err=True)
+        raise typer.Exit(code=1) from error
+
+    logger.info("Upserted %s authors and %s series", author_count, series_count)
+
+
+def upsert_authors_from_csv(session: Session, authors_csv: Path) -> int:
+    """Upsert authors from a CSV file."""
+    count = 0
+    for row_number, row in csv_rows(authors_csv):
+        name = required_csv_value(row, authors_csv, row_number, NAME_COLUMN)
+        upsert_author(session, name, csv_id(row, authors_csv, row_number))
+        count += 1
+    return count
+
+
+def upsert_series_from_csv(session: Session, series_csv: Path) -> int:
+    """Upsert series from a CSV file."""
+    count = 0
+    for row_number, row in csv_rows(series_csv):
+        series_name = required_csv_value(row, series_csv, row_number, NAME_COLUMN)
+        author_name = required_csv_value(row, series_csv, row_number, AUTHOR_NAME_COLUMN)
+        author = find_author_by_name(session, author_name)
+        if author is None:
+            msg = f"{series_csv}:{row_number}: author not found: {author_name}"
+            raise CatalogImportError(msg)
+        upsert_series(session, series_name, author, csv_id(row, series_csv, row_number))
+        count += 1
+    return count
+
+
+def upsert_author(session: Session, name: str, author_id: int | None) -> AudiobookAuthor:
+    """Upsert one author by id or exact name."""
+    if author_id is not None:
+        author = session.get(AudiobookAuthor, author_id)
+        if author is None:
+            author = AudiobookAuthor(id=author_id, name=name)
+            session.add(author)
+        else:
+            author.name = name
+        session.flush()
+        return author
+
+    author = find_author_by_name(session, name)
+    if author is None:
+        author = AudiobookAuthor(name=name)
+        session.add(author)
+        session.flush()
+    return author
+
+
+def upsert_series(
+    session: Session,
+    name: str,
+    author: AudiobookAuthor,
+    series_id: int | None,
+) -> AudiobookSeries:
+    """Upsert one series by id or exact author/name match."""
+    if series_id is not None:
+        series = session.get(AudiobookSeries, series_id)
+        if series is None:
+            series = AudiobookSeries(id=series_id, name=name, author=author)
+            session.add(series)
+        else:
+            series.name = name
+            series.author = author
+        session.flush()
+        return series
+
+    series = find_series_by_name_and_author(session, name, author.id)
+    if series is None:
+        series = AudiobookSeries(name=name, author=author)
+        session.add(series)
+        session.flush()
+    return series
+
+
+def find_author_by_name(session: Session, name: str) -> AudiobookAuthor | None:
+    """Find one author by exact name."""
+    return session.scalar(select(AudiobookAuthor).where(AudiobookAuthor.name == name))
+
+
+def find_series_by_name_and_author(
+    session: Session,
+    name: str,
+    author_id: int,
+) -> AudiobookSeries | None:
+    """Find one series by exact name and author."""
+    return session.scalar(
+        select(AudiobookSeries).where(
+            AudiobookSeries.name == name,
+            AudiobookSeries.author_id == author_id,
+        ),
+    )
+
+
+def csv_rows(csv_path: Path) -> list[tuple[int, dict[str, str | None]]]:
+    """Read a CSV file as numbered rows."""
+    with csv_path.open(newline="", encoding="utf-8") as file:
+        reader = csv.DictReader(file)
+        if reader.fieldnames is None:
+            msg = f"{csv_path}: missing CSV header"
+            raise CatalogImportError(msg)
+        return [(row_number, row) for row_number, row in enumerate(reader, start=2)]
+
+
+def required_csv_value(
+    row: dict[str, str | None],
+    csv_path: Path,
+    row_number: int,
+    column: str,
+) -> str:
+    """Read a required CSV value."""
+    value = row.get(column)
+    if value and value.strip():
+        return value.strip()
+    msg = f"{csv_path}:{row_number}: missing required column value: {column}"
+    raise CatalogImportError(msg)
+
+
+def csv_id(row: dict[str, str | None], csv_path: Path, row_number: int) -> int | None:
+    """Read an optional id field from a CSV row."""
+    value = row.get(ID_COLUMN)
+    if value is None or not value.strip():
+        return None
+    try:
+        return int(value)
+    except ValueError as error:
+        msg = f"{csv_path}:{row_number}: id must be an integer: {value}"
+        raise CatalogImportError(msg) from error
+    return None
+
+
+if __name__ == "__main__":
+    typer.run(main)

systems/bob/default.nix

@@ -28,12 +28,7 @@
   networking = {
     hostName = "bob";
     hostId = "7c678a41";
-    firewall = {
+    firewall.enable = true;
-      enable = true;
-      allowedTCPPorts = [
-        8000
-      ];
-    };
     networkmanager.enable = true;
   };
 

systems/bob/hardware.nix

@@ -30,11 +30,6 @@
         keyFile = "/dev/disk/by-id/usb-Samsung_Flash_Drive_FIT_0374620080067131-0:0";
       };
     };
-
-    zfs.extraPools = [
-      "storage"
-    ];
-
     kernelModules = [ "kvm-amd" ];
     extraModulePackages = [ ];
   };

tests/test_gitea_flake_lock.py

@@ -1,113 +0,0 @@
-"""Tests for Gitea flake.lock automation."""
-
-from __future__ import annotations
-
-from python.gitea import PullRequest
-from python.gitea_flake_lock import (
-    PR_CHECK_WORKFLOWS,
-    PR_LABELS,
-    dispatch_pull_request_checks,
-    ensure_flake_lock_pull_request,
-    find_flake_lock_pull_request,
-)
-
-
-def _pull_request(number=1, head_branch="automation/update-flake-lock"):
-    return PullRequest(
-        number=number,
-        title="Update flake.lock",
-        html_url=f"https://gitea.example.test/pulls/{number}",
-        labels=(),
-        head_branch=head_branch,
-        base_branch="main",
-    )
-
-
-class FakeGiteaClient:
-    def __init__(self, pull_requests=None):
-        self.pull_requests = pull_requests or []
-        self.dispatch_calls = []
-        self.list_calls = []
-        self.create_calls = []
-
-    def list_open_pull_requests(self, **kwargs):
-        self.list_calls.append(kwargs)
-        return self.pull_requests
-
-    def create_pull_request(self, **kwargs):
-        self.create_calls.append(kwargs)
-        return _pull_request()
-
-    def dispatch_workflow(self, **kwargs):
-        self.dispatch_calls.append(kwargs)
-
-
-def test_ensure_flake_lock_pull_request_finds_by_branch():
-    pull_request = _pull_request()
-    client = FakeGiteaClient([pull_request])
-
-    result = ensure_flake_lock_pull_request(
-        client,
-        owner="Richie",
-        repo="dotfiles",
-        branch="automation/update-flake-lock",
-        base="main",
-    )
-
-    assert result == pull_request
-    assert client.list_calls == [
-        {"owner": "Richie", "repo": "dotfiles", "head": "automation/update-flake-lock"},
-    ]
-    assert client.create_calls == []
-
-
-def test_ensure_flake_lock_pull_request_creates_with_labels():
-    client = FakeGiteaClient()
-
-    ensure_flake_lock_pull_request(
-        client,
-        owner="Richie",
-        repo="dotfiles",
-        branch="automation/update-flake-lock",
-        base="main",
-    )
-
-    assert client.create_calls == [
-        {
-            "owner": "Richie",
-            "repo": "dotfiles",
-            "title": "Update flake.lock",
-            "body": "Automated flake.lock update.",
-            "head": "automation/update-flake-lock",
-            "base": "main",
-            "labels": PR_LABELS,
-        },
-    ]
-
-
-def test_find_flake_lock_pull_request_finds_by_label():
-    pull_request = _pull_request()
-    client = FakeGiteaClient([pull_request])
-
-    result = find_flake_lock_pull_request(client, owner="Richie", repo="dotfiles")
-
-    assert result == pull_request
-    assert client.list_calls == [
-        {"owner": "Richie", "repo": "dotfiles", "labels": ["flake_lock_update"]},
-    ]
-
-
-def test_dispatch_pull_request_checks_runs_each_workflow():
-    client = FakeGiteaClient()
-
-    dispatch_pull_request_checks(client, owner="Richie", repo="dotfiles", branch="automation/update-flake-lock")
-
-    assert client.dispatch_calls == [
-        {
-            "owner": "Richie",
-            "repo": "dotfiles",
-            "workflow_id": workflow,
-            "ref": "automation/update-flake-lock",
-        }
-        for workflow in PR_CHECK_WORKFLOWS
-    ]

users/richie/home/gui/llm_tools.nix

@@ -1,7 +1,7 @@
 { pkgs, ... }:
 {
   home.packages = [
-    pkgs.master.claude-code
+    pkgs.claude-code
     pkgs.master.codex
     pkgs.master.opencode
     pkgs.master.pi-coding-agent