updating signing.format for programs.git

Flake lock file updates:

• Updated input 'firefox-addons':
    'gitlab:rycee/nur-expressions/81e28f4?dir=pkgs/firefox-addons' (2026-03-20)
  → 'gitlab:rycee/nur-expressions/0581568?dir=pkgs/firefox-addons' (2026-04-17)
• Updated input 'home-manager':
    'github:nix-community/home-manager/9670de2' (2026-03-20)
  → 'github:nix-community/home-manager/565e534' (2026-04-17)
• Updated input 'nixos-hardware':
    'github:nixos/nixos-hardware/2d4b471' (2026-03-20)
  → 'github:nixos/nixos-hardware/c775c27' (2026-04-06)
• Updated input 'nixpkgs':
    'github:nixos/nixpkgs/b40629e' (2026-03-18)
  → 'github:nixos/nixpkgs/4bd9165' (2026-04-14)
• Updated input 'nixpkgs-master':
    'github:nixos/nixpkgs/8620c0b' (2026-03-21)
  → 'github:nixos/nixpkgs/025c852' (2026-04-17)
• Updated input 'sops-nix':
    'github:Mic92/sops-nix/29b6519' (2026-03-19)
  → 'github:Mic92/sops-nix/d4971dd' (2026-04-13)

.vscode/settings.json

@@ -40,7 +40,6 @@
     "cgroupdriver",
     "charliermarsh",
     "Checkpointing",
-    "cloudflared",
     "codellama",
     "codezombiech",
     "compactmode",
@@ -308,6 +307,7 @@
     "usernamehw",
     "userprefs",
     "vaninventory",
+    "vdev",
     "vfat",
     "victron",
     "virt",

README.md

@@ -1,26 +1 @@
 # dotfiles
-
-<!-- LINE-COUNT-START -->
-This repo has **20,055** lines of technical debt.
-
-| File Type | Lines | Percentage |
-|-----------|------:|-----------:|
-| .py | 11,441 | 57.0% |
-| .nix | 4,471 | 22.3% |
-| .yaml | 1,121 | 5.6% |
-| .html | 1,009 | 5.0% |
-| .json | 555 | 2.8% |
-| .yml | 479 | 2.4% |
-| .toml | 290 | 1.4% |
-| .css | 212 | 1.1% |
-| .gitignore | 199 | 1.0% |
-| .md | 75 | 0.4% |
-| .cfg | 73 | 0.4% |
-| .sh | 48 | 0.2% |
-| .mako | 36 | 0.2% |
-| .LICENSE | 21 | 0.1% |
-| .conf | 17 | 0.1% |
-| .Gemfile | 4 | 0.0% |
-| .svg | 3 | 0.0% |
-| .new | 1 | 0.0% |
-<!-- LINE-COUNT-END -->

common/global/nix.nix

@@ -34,6 +34,7 @@ in
       warn-dirty = false;
       flake-registry = ""; # disable global flake registries
       connect-timeout = 10;
+      download-buffer-size = 536870912;
       fallback = true;
     };
 

common/optional/syncthing_base.nix

@@ -12,7 +12,7 @@
       brain.id = "SSCGIPI-IV3VYKB-TRNIJE3-COV4T2H-CDBER7F-I2CGHYA-NWOEUDU-3T5QAAN"; # cspell:disable-line
       ipad.id = "KI76T3X-SFUGV2L-VSNYTKR-TSIUV5L-SHWD3HE-GQRGRCN-GY4UFMD-CW6Z6AX"; # cspell:disable-line
       jeeves.id = "ICRHXZW-ECYJCUZ-I4CZ64R-3XRK7CG-LL2HAAK-FGOHD22-BQA4AI6-5OAL6AG"; # cspell:disable-line
-      phone.id = "TBRULKD-7DZPGGZ-F6LLB7J-MSO54AY-7KLPBIN-QOFK6PX-W2HBEWI-PHM2CQI"; # cspell:disable-line
+      phone.id = "JPVQKQW-CFXOJXT-Q5G5F3H-QIDHDRE-GKHPTQB-GXZUQSP-U7FR7F7-INP3AAH"; # cspell:disable-line
       rhapsody-in-green.id = "ASL3KC4-3XEN6PA-7BQBRKE-A7JXLI6-DJT43BY-Q4WPOER-7UALUAZ-VTPQ6Q4"; # cspell:disable-line
     };
   };

flake.lock

@@ -8,11 +8,11 @@
       },
       "locked": {
         "dir": "pkgs/firefox-addons",
-        "lastModified": 1773979456,
+        "lastModified": 1776398575,
-        "narHash": "sha256-9kBMJ5IvxqNlkkj/swmE8uK1Sc7TL/LIRUI958m7uBM=",
+        "narHash": "sha256-WArU6WOdWxzbzGqYk4w1Mucg+bw/SCl6MoSp+/cZMio=",
         "owner": "rycee",
         "repo": "nur-expressions",
-        "rev": "81e28f47ac18d9e89513929c77e711e657b64851",
+        "rev": "05815686caf4e3678f5aeb5fd36e567886ab0d30",
         "type": "gitlab"
       },
       "original": {
@@ -29,11 +29,11 @@
         ]
       },
       "locked": {
-        "lastModified": 1774007980,
+        "lastModified": 1776454077,
-        "narHash": "sha256-FOnZjElEI8pqqCvB6K/1JRHTE8o4rer8driivTpq2uo=",
+        "narHash": "sha256-7zSUFWsU0+jlD7WB3YAxQ84Z/iJurA5hKPm8EfEyGJk=",
         "owner": "nix-community",
         "repo": "home-manager",
-        "rev": "9670de2921812bc4e0452f6e3efd8c859696c183",
+        "rev": "565e5349208fe7d0831ef959103c9bafbeac0681",
         "type": "github"
       },
       "original": {
@@ -44,11 +44,11 @@
     },
     "nixos-hardware": {
       "locked": {
-        "lastModified": 1774018263,
+        "lastModified": 1775490113,
-        "narHash": "sha256-HHYEwK1A22aSaxv2ibhMMkKvrDGKGlA/qObG4smrSqc=",
+        "narHash": "sha256-2ZBhDNZZwYkRmefK5XLOusCJHnoeKkoN95hoSGgMxWM=",
         "owner": "nixos",
         "repo": "nixos-hardware",
-        "rev": "2d4b4717b2534fad5c715968c1cece04a172b365",
+        "rev": "c775c2772ba56e906cbeb4e0b2db19079ef11ff7",
         "type": "github"
       },
       "original": {
@@ -60,11 +60,11 @@
     },
     "nixpkgs": {
       "locked": {
-        "lastModified": 1773821835,
+        "lastModified": 1776169885,
-        "narHash": "sha256-TJ3lSQtW0E2JrznGVm8hOQGVpXjJyXY2guAxku2O9A4=",
+        "narHash": "sha256-l/iNYDZ4bGOAFQY2q8y5OAfBBtrDAaPuRQqWaFHVRXM=",
         "owner": "nixos",
         "repo": "nixpkgs",
-        "rev": "b40629efe5d6ec48dd1efba650c797ddbd39ace0",
+        "rev": "4bd9165a9165d7b5e33ae57f3eecbcb28fb231c9",
         "type": "github"
       },
       "original": {
@@ -76,11 +76,11 @@
     },
     "nixpkgs-master": {
       "locked": {
-        "lastModified": 1774051532,
+        "lastModified": 1776469842,
-        "narHash": "sha256-d3CGMweyYIcPuTj5BKq+1Lx4zwlgL31nVtN647tOZKo=",
+        "narHash": "sha256-sqzM6PKMQoGk8Sl+uv2sbP1qiS2SPQhA2yn5zgZINMc=",
         "owner": "nixos",
         "repo": "nixpkgs",
-        "rev": "8620c0b5cc8fbe76502442181be1d0514bc3a1b7",
+        "rev": "025c852a89be820b3117f604c8ace42e9b4caa08",
         "type": "github"
       },
       "original": {
@@ -125,11 +125,11 @@
         ]
       },
       "locked": {
-        "lastModified": 1773889674,
+        "lastModified": 1776119890,
-        "narHash": "sha256-+ycaiVAk3MEshJTg35cBTUa0MizGiS+bgpYw/f8ohkg=",
+        "narHash": "sha256-Zm6bxLNnEOYuS/SzrAGsYuXSwk3cbkRQZY0fJnk8a5M=",
         "owner": "Mic92",
         "repo": "sops-nix",
-        "rev": "29b6519f3e0780452bca0ac0be4584f04ac16cc5",
+        "rev": "d4971dd58c6627bfee52a1ad4237637c0a2fb0cd",
         "type": "github"
       },
       "original": {

overlays/default.nix

@@ -23,8 +23,10 @@
         apscheduler
         fastapi
         fastapi-cli
+        faster-whisper
         httpx
         mypy
+        orjson
         polars
         psycopg
         pydantic

pyproject.toml

@@ -26,6 +26,7 @@ dependencies = [
 [project.scripts]
 database = "python.database_cli:app"
 van-inventory = "python.van_inventory.main:serve"
+whisper-transcribe = "python.tools.whisper.transcribe:main"
 
 [dependency-groups]
 dev = [
@@ -50,6 +51,7 @@ lint.ignore = [
     "COM812", # (TEMP) conflicts when used with the formatter
     "ISC001", # (TEMP) conflicts when used with the formatter
     "S603",   # (PERM) This is known to cause a false positive
+    "S607",   # (PERM) This is becoming a consistent annoyance
 ]
 
 [tool.ruff.lint.per-file-ignores]
@@ -78,9 +80,7 @@ lint.ignore = [
 "python/congress_tracker/**" = [
     "TC003", # (perm) this creates issues because sqlalchemy uses these at runtime
 ]
-"python/eval_warnings/**" = [
+
-    "S607", # (perm) gh and git are expected on PATH in the runner environment
-]
 "python/alembic/**" = [
     "INP001", # (perm) this creates LSP issues for alembic
 ]

python/alembic/data_science_dev/versions/2026_03_24-adding_failedingestion_2f43120e3ffc.py

@@ -0,0 +1,50 @@
+"""adding FailedIngestion.
+
+Revision ID: 2f43120e3ffc
+Revises: f99be864fe69
+Create Date: 2026-03-24 23:46:17.277897
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+import sqlalchemy as sa
+from alembic import op
+
+from python.orm import DataScienceDevBase
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "2f43120e3ffc"
+down_revision: str | None = "f99be864fe69"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+schema = DataScienceDevBase.schema_name
+
+
+def upgrade() -> None:
+    """Upgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "failed_ingestion",
+        sa.Column("raw_line", sa.Text(), nullable=False),
+        sa.Column("error", sa.Text(), 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_failed_ingestion")),
+        schema=schema,
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Downgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("failed_ingestion", schema=schema)
+    # ### end Alembic commands ###

python/alembic/data_science_dev/versions/2026_03_25-attach_partitions_to_posts.py

@@ -0,0 +1,72 @@
+"""Attach all partition tables to the posts parent table.
+
+Alembic autogenerate creates partition tables as standalone tables but does not
+emit the ALTER TABLE ... ATTACH PARTITION statements needed for PostgreSQL to
+route inserts to the correct partition.
+
+Revision ID: a1b2c3d4e5f6
+Revises: 605b1794838f
+Create Date: 2026-03-25 10:00:00.000000
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+from alembic import op
+from sqlalchemy import text
+
+from python.orm import DataScienceDevBase
+from python.orm.data_science_dev.posts.partitions import (
+    PARTITION_END_YEAR,
+    PARTITION_START_YEAR,
+    iso_weeks_in_year,
+    week_bounds,
+)
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "a1b2c3d4e5f6"
+down_revision: str | None = "605b1794838f"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+schema = DataScienceDevBase.schema_name
+
+ALREADY_ATTACHED_QUERY = text("""
+    SELECT inhrelid::regclass::text
+    FROM pg_inherits
+    WHERE inhparent = :parent::regclass
+""")
+
+
+def upgrade() -> None:
+    """Attach all weekly partition tables to the posts parent table."""
+    connection = op.get_bind()
+    already_attached = {row[0] for row in connection.execute(ALREADY_ATTACHED_QUERY, {"parent": f"{schema}.posts"})}
+
+    for year in range(PARTITION_START_YEAR, PARTITION_END_YEAR + 1):
+        for week in range(1, iso_weeks_in_year(year) + 1):
+            table_name = f"posts_{year}_{week:02d}"
+            qualified_name = f"{schema}.{table_name}"
+            if qualified_name in already_attached:
+                continue
+            start, end = week_bounds(year, week)
+            start_str = start.strftime("%Y-%m-%d %H:%M:%S")
+            end_str = end.strftime("%Y-%m-%d %H:%M:%S")
+            op.execute(
+                f"ALTER TABLE {schema}.posts "
+                f"ATTACH PARTITION {qualified_name} "
+                f"FOR VALUES FROM ('{start_str}') TO ('{end_str}')"
+            )
+
+
+def downgrade() -> None:
+    """Detach all weekly partition tables from the posts parent table."""
+    for year in range(PARTITION_START_YEAR, PARTITION_END_YEAR + 1):
+        for week in range(1, iso_weeks_in_year(year) + 1):
+            table_name = f"posts_{year}_{week:02d}"
+            op.execute(f"ALTER TABLE {schema}.posts DETACH PARTITION {schema}.{table_name}")

python/alembic/data_science_dev/versions/2026_03_27-adding_congress_data_83bfc8af92d8.py

@@ -0,0 +1,153 @@
+"""adding congress data.
+
+Revision ID: 83bfc8af92d8
+Revises: a1b2c3d4e5f6
+Create Date: 2026-03-27 10:43:02.324510
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+import sqlalchemy as sa
+from alembic import op
+
+from python.orm import DataScienceDevBase
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "83bfc8af92d8"
+down_revision: str | None = "a1b2c3d4e5f6"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+schema = DataScienceDevBase.schema_name
+
+
+def upgrade() -> None:
+    """Upgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "bill",
+        sa.Column("congress", sa.Integer(), nullable=False),
+        sa.Column("bill_type", sa.String(), nullable=False),
+        sa.Column("number", sa.Integer(), nullable=False),
+        sa.Column("title", sa.String(), nullable=True),
+        sa.Column("title_short", sa.String(), nullable=True),
+        sa.Column("official_title", sa.String(), nullable=True),
+        sa.Column("status", sa.String(), nullable=True),
+        sa.Column("status_at", sa.Date(), nullable=True),
+        sa.Column("sponsor_bioguide_id", sa.String(), nullable=True),
+        sa.Column("subjects_top_term", sa.String(), nullable=True),
+        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_bill")),
+        sa.UniqueConstraint("congress", "bill_type", "number", name="uq_bill_congress_type_number"),
+        schema=schema,
+    )
+    op.create_index("ix_bill_congress", "bill", ["congress"], unique=False, schema=schema)
+    op.create_table(
+        "legislator",
+        sa.Column("bioguide_id", sa.Text(), nullable=False),
+        sa.Column("thomas_id", sa.String(), nullable=True),
+        sa.Column("lis_id", sa.String(), nullable=True),
+        sa.Column("govtrack_id", sa.Integer(), nullable=True),
+        sa.Column("opensecrets_id", sa.String(), nullable=True),
+        sa.Column("fec_ids", sa.String(), nullable=True),
+        sa.Column("first_name", sa.String(), nullable=False),
+        sa.Column("last_name", sa.String(), nullable=False),
+        sa.Column("official_full_name", sa.String(), nullable=True),
+        sa.Column("nickname", sa.String(), nullable=True),
+        sa.Column("birthday", sa.Date(), nullable=True),
+        sa.Column("gender", sa.String(), nullable=True),
+        sa.Column("current_party", sa.String(), nullable=True),
+        sa.Column("current_state", sa.String(), nullable=True),
+        sa.Column("current_district", sa.Integer(), nullable=True),
+        sa.Column("current_chamber", sa.String(), nullable=True),
+        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_legislator")),
+        schema=schema,
+    )
+    op.create_index(op.f("ix_legislator_bioguide_id"), "legislator", ["bioguide_id"], unique=True, schema=schema)
+    op.create_table(
+        "bill_text",
+        sa.Column("bill_id", sa.Integer(), nullable=False),
+        sa.Column("version_code", sa.String(), nullable=False),
+        sa.Column("version_name", sa.String(), nullable=True),
+        sa.Column("text_content", sa.String(), nullable=True),
+        sa.Column("date", sa.Date(), nullable=True),
+        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(
+            ["bill_id"], [f"{schema}.bill.id"], name=op.f("fk_bill_text_bill_id_bill"), ondelete="CASCADE"
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_bill_text")),
+        sa.UniqueConstraint("bill_id", "version_code", name="uq_bill_text_bill_id_version_code"),
+        schema=schema,
+    )
+    op.create_table(
+        "vote",
+        sa.Column("congress", sa.Integer(), nullable=False),
+        sa.Column("chamber", sa.String(), nullable=False),
+        sa.Column("session", sa.Integer(), nullable=False),
+        sa.Column("number", sa.Integer(), nullable=False),
+        sa.Column("vote_type", sa.String(), nullable=True),
+        sa.Column("question", sa.String(), nullable=True),
+        sa.Column("result", sa.String(), nullable=True),
+        sa.Column("result_text", sa.String(), nullable=True),
+        sa.Column("vote_date", sa.Date(), nullable=False),
+        sa.Column("yea_count", sa.Integer(), nullable=True),
+        sa.Column("nay_count", sa.Integer(), nullable=True),
+        sa.Column("not_voting_count", sa.Integer(), nullable=True),
+        sa.Column("present_count", sa.Integer(), nullable=True),
+        sa.Column("bill_id", sa.Integer(), nullable=True),
+        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(["bill_id"], [f"{schema}.bill.id"], name=op.f("fk_vote_bill_id_bill")),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_vote")),
+        sa.UniqueConstraint("congress", "chamber", "session", "number", name="uq_vote_congress_chamber_session_number"),
+        schema=schema,
+    )
+    op.create_index("ix_vote_congress_chamber", "vote", ["congress", "chamber"], unique=False, schema=schema)
+    op.create_index("ix_vote_date", "vote", ["vote_date"], unique=False, schema=schema)
+    op.create_table(
+        "vote_record",
+        sa.Column("vote_id", sa.Integer(), nullable=False),
+        sa.Column("legislator_id", sa.Integer(), nullable=False),
+        sa.Column("position", sa.String(), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["legislator_id"],
+            [f"{schema}.legislator.id"],
+            name=op.f("fk_vote_record_legislator_id_legislator"),
+            ondelete="CASCADE",
+        ),
+        sa.ForeignKeyConstraint(
+            ["vote_id"], [f"{schema}.vote.id"], name=op.f("fk_vote_record_vote_id_vote"), ondelete="CASCADE"
+        ),
+        sa.PrimaryKeyConstraint("vote_id", "legislator_id", name=op.f("pk_vote_record")),
+        schema=schema,
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Downgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("vote_record", schema=schema)
+    op.drop_index("ix_vote_date", table_name="vote", schema=schema)
+    op.drop_index("ix_vote_congress_chamber", table_name="vote", schema=schema)
+    op.drop_table("vote", schema=schema)
+    op.drop_table("bill_text", schema=schema)
+    op.drop_index(op.f("ix_legislator_bioguide_id"), table_name="legislator", schema=schema)
+    op.drop_table("legislator", schema=schema)
+    op.drop_index("ix_bill_congress", table_name="bill", schema=schema)
+    op.drop_table("bill", schema=schema)
+    # ### end Alembic commands ###

python/alembic/data_science_dev/versions/2026_03_29-adding_legislatorsocialmedia_5cd7eee3549d.py

@@ -0,0 +1,58 @@
+"""adding LegislatorSocialMedia.
+
+Revision ID: 5cd7eee3549d
+Revises: 83bfc8af92d8
+Create Date: 2026-03-29 11:53:44.224799
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+import sqlalchemy as sa
+from alembic import op
+
+from python.orm import DataScienceDevBase
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "5cd7eee3549d"
+down_revision: str | None = "83bfc8af92d8"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+schema = DataScienceDevBase.schema_name
+
+
+def upgrade() -> None:
+    """Upgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "legislator_social_media",
+        sa.Column("legislator_id", sa.Integer(), nullable=False),
+        sa.Column("platform", sa.String(), nullable=False),
+        sa.Column("account_name", sa.String(), nullable=False),
+        sa.Column("url", sa.String(), nullable=True),
+        sa.Column("source", 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.ForeignKeyConstraint(
+            ["legislator_id"],
+            [f"{schema}.legislator.id"],
+            name=op.f("fk_legislator_social_media_legislator_id_legislator"),
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_legislator_social_media")),
+        schema=schema,
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Downgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("legislator_social_media", schema=schema)
+    # ### end Alembic commands ###

python/alembic/env.py

@@ -81,6 +81,7 @@ def include_name(
 
     """
     if type_ == "schema":
+        # allows a database with multiple schemas to have separate alembic revisions
         return name == target_metadata.schema
     return True
 

python/alembic/richie/versions/2026_03_29-removed_ds_table_from_richie_db_c8a794340928.py

@@ -0,0 +1,187 @@
+"""removed ds table from richie DB.
+
+Revision ID: c8a794340928
+Revises: 6b275323f435
+Create Date: 2026-03-29 15:29:23.643146
+
+"""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
+import sqlalchemy as sa
+from alembic import op
+from sqlalchemy.dialects import postgresql
+
+from python.orm import RichieBase
+
+if TYPE_CHECKING:
+    from collections.abc import Sequence
+
+# revision identifiers, used by Alembic.
+revision: str = "c8a794340928"
+down_revision: str | None = "6b275323f435"
+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.drop_table("vote_record", schema=schema)
+    op.drop_index(op.f("ix_vote_congress_chamber"), table_name="vote", schema=schema)
+    op.drop_index(op.f("ix_vote_date"), table_name="vote", schema=schema)
+    op.drop_index(op.f("ix_legislator_bioguide_id"), table_name="legislator", schema=schema)
+    op.drop_table("legislator", schema=schema)
+    op.drop_table("vote", schema=schema)
+    op.drop_index(op.f("ix_bill_congress"), table_name="bill", schema=schema)
+    op.drop_table("bill", schema=schema)
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Downgrade."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "vote",
+        sa.Column("congress", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("chamber", sa.VARCHAR(), autoincrement=False, nullable=False),
+        sa.Column("session", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("number", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("vote_type", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("question", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("result", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("result_text", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("vote_date", sa.DATE(), autoincrement=False, nullable=False),
+        sa.Column("yea_count", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("nay_count", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("not_voting_count", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("present_count", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("bill_id", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("id", sa.INTEGER(), autoincrement=True, nullable=False),
+        sa.Column(
+            "created",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.Column(
+            "updated",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.ForeignKeyConstraint(["bill_id"], [f"{schema}.bill.id"], name=op.f("fk_vote_bill_id_bill")),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_vote")),
+        sa.UniqueConstraint(
+            "congress",
+            "chamber",
+            "session",
+            "number",
+            name=op.f("uq_vote_congress_chamber_session_number"),
+            postgresql_include=[],
+            postgresql_nulls_not_distinct=False,
+        ),
+        schema=schema,
+    )
+    op.create_index(op.f("ix_vote_date"), "vote", ["vote_date"], unique=False, schema=schema)
+    op.create_index(op.f("ix_vote_congress_chamber"), "vote", ["congress", "chamber"], unique=False, schema=schema)
+    op.create_table(
+        "vote_record",
+        sa.Column("vote_id", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("legislator_id", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("position", sa.VARCHAR(), autoincrement=False, nullable=False),
+        sa.ForeignKeyConstraint(
+            ["legislator_id"],
+            [f"{schema}.legislator.id"],
+            name=op.f("fk_vote_record_legislator_id_legislator"),
+            ondelete="CASCADE",
+        ),
+        sa.ForeignKeyConstraint(
+            ["vote_id"], [f"{schema}.vote.id"], name=op.f("fk_vote_record_vote_id_vote"), ondelete="CASCADE"
+        ),
+        sa.PrimaryKeyConstraint("vote_id", "legislator_id", name=op.f("pk_vote_record")),
+        schema=schema,
+    )
+    op.create_table(
+        "legislator",
+        sa.Column("bioguide_id", sa.TEXT(), autoincrement=False, nullable=False),
+        sa.Column("thomas_id", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("lis_id", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("govtrack_id", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("opensecrets_id", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("fec_ids", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("first_name", sa.VARCHAR(), autoincrement=False, nullable=False),
+        sa.Column("last_name", sa.VARCHAR(), autoincrement=False, nullable=False),
+        sa.Column("official_full_name", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("nickname", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("birthday", sa.DATE(), autoincrement=False, nullable=True),
+        sa.Column("gender", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("current_party", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("current_state", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("current_district", sa.INTEGER(), autoincrement=False, nullable=True),
+        sa.Column("current_chamber", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("id", sa.INTEGER(), autoincrement=True, nullable=False),
+        sa.Column(
+            "created",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.Column(
+            "updated",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_legislator")),
+        schema=schema,
+    )
+    op.create_index(op.f("ix_legislator_bioguide_id"), "legislator", ["bioguide_id"], unique=True, schema=schema)
+    op.create_table(
+        "bill",
+        sa.Column("congress", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("bill_type", sa.VARCHAR(), autoincrement=False, nullable=False),
+        sa.Column("number", sa.INTEGER(), autoincrement=False, nullable=False),
+        sa.Column("title", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("title_short", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("official_title", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("status", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("status_at", sa.DATE(), autoincrement=False, nullable=True),
+        sa.Column("sponsor_bioguide_id", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("subjects_top_term", sa.VARCHAR(), autoincrement=False, nullable=True),
+        sa.Column("id", sa.INTEGER(), autoincrement=True, nullable=False),
+        sa.Column(
+            "created",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.Column(
+            "updated",
+            postgresql.TIMESTAMP(timezone=True),
+            server_default=sa.text("now()"),
+            autoincrement=False,
+            nullable=False,
+        ),
+        sa.PrimaryKeyConstraint("id", name=op.f("pk_bill")),
+        sa.UniqueConstraint(
+            "congress",
+            "bill_type",
+            "number",
+            name=op.f("uq_bill_congress_type_number"),
+            postgresql_include=[],
+            postgresql_nulls_not_distinct=False,
+        ),
+        schema=schema,
+    )
+    op.create_index(op.f("ix_bill_congress"), "bill", ["congress"], unique=False, schema=schema)
+    # ### end Alembic commands ###

python/data_science/__init__.py

@@ -0,0 +1,3 @@
+"""Data science CLI tools."""
+
+from __future__ import annotations

python/data_science/ingest_congress.py

@@ -0,0 +1,613 @@
+"""Ingestion pipeline for loading congress data from unitedstates/congress JSON files.
+
+Loads legislators, bills, votes, vote records, and bill text into the data_science_dev database.
+Expects the parent directory to contain congress-tracker/ and congress-legislators/ as siblings.
+
+Usage:
+    ingest-congress /path/to/parent/
+    ingest-congress /path/to/parent/ --congress 118
+    ingest-congress /path/to/parent/ --congress 118 --only bills
+"""
+
+from __future__ import annotations
+
+import logging
+from pathlib import Path  # noqa: TC003 needed at runtime for typer CLI argument
+from typing import TYPE_CHECKING, Annotated
+
+import orjson
+import typer
+import yaml
+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.data_science_dev.congress import Bill, BillText, Legislator, LegislatorSocialMedia, Vote, VoteRecord
+
+if TYPE_CHECKING:
+    from collections.abc import Iterator
+
+    from sqlalchemy.engine import Engine
+
+logger = logging.getLogger(__name__)
+
+BATCH_SIZE = 10_000
+
+app = typer.Typer(help="Ingest unitedstates/congress data into data_science_dev.")
+
+
+@app.command()
+def main(
+    parent_dir: Annotated[
+        Path,
+        typer.Argument(help="Parent directory containing congress-tracker/ and congress-legislators/"),
+    ],
+    congress: Annotated[int | None, typer.Option(help="Only ingest a specific congress number")] = None,
+    only: Annotated[
+        str | None,
+        typer.Option(help="Only run a specific step: legislators, social-media, bills, votes, bill-text"),
+    ] = None,
+) -> None:
+    """Ingest congress data from unitedstates/congress JSON files."""
+    configure_logger(level="INFO")
+
+    data_dir = parent_dir / "congress-tracker/congress/data/"
+    legislators_dir = parent_dir / "congress-legislators"
+
+    if not data_dir.is_dir():
+        typer.echo(f"Expected congress-tracker/ directory: {data_dir}", err=True)
+        raise typer.Exit(code=1)
+
+    if not legislators_dir.is_dir():
+        typer.echo(f"Expected congress-legislators/ directory: {legislators_dir}", err=True)
+        raise typer.Exit(code=1)
+
+    engine = get_postgres_engine(name="DATA_SCIENCE_DEV")
+
+    congress_dirs = _resolve_congress_dirs(data_dir, congress)
+    if not congress_dirs:
+        typer.echo("No congress directories found.", err=True)
+        raise typer.Exit(code=1)
+
+    logger.info("Found %d congress directories to process", len(congress_dirs))
+
+    steps: dict[str, tuple] = {
+        "legislators": (ingest_legislators, (engine, legislators_dir)),
+        "legislators-social-media": (ingest_social_media, (engine, legislators_dir)),
+        "bills": (ingest_bills, (engine, congress_dirs)),
+        "votes": (ingest_votes, (engine, congress_dirs)),
+        "bill-text": (ingest_bill_text, (engine, congress_dirs)),
+    }
+
+    if only:
+        if only not in steps:
+            typer.echo(f"Unknown step: {only}. Choose from: {', '.join(steps)}", err=True)
+            raise typer.Exit(code=1)
+        steps = {only: steps[only]}
+
+    for step_name, (step_func, step_args) in steps.items():
+        logger.info("=== Starting step: %s ===", step_name)
+        step_func(*step_args)
+        logger.info("=== Finished step: %s ===", step_name)
+
+    logger.info("ingest-congress done")
+
+
+def _resolve_congress_dirs(data_dir: Path, congress: int | None) -> list[Path]:
+    """Find congress number directories under data_dir."""
+    if congress is not None:
+        target = data_dir / str(congress)
+        return [target] if target.is_dir() else []
+    return sorted(path for path in data_dir.iterdir() if path.is_dir() and path.name.isdigit())
+
+
+def _flush_batch(session: Session, batch: list[object], label: str) -> int:
+    """Add a batch of ORM objects to the session and commit. Returns count added."""
+    if not batch:
+        return 0
+    session.add_all(batch)
+    session.commit()
+    count = len(batch)
+    logger.info("Committed %d %s", count, label)
+    batch.clear()
+    return count
+
+
+# ---------------------------------------------------------------------------
+# Legislators — loaded from congress-legislators YAML files
+# ---------------------------------------------------------------------------
+
+
+def ingest_legislators(engine: Engine, legislators_dir: Path) -> None:
+    """Load legislators from congress-legislators YAML files."""
+    legislators_data = _load_legislators_yaml(legislators_dir)
+    logger.info("Loaded %d legislators from YAML files", len(legislators_data))
+
+    with Session(engine) as session:
+        existing_legislators = {
+            legislator.bioguide_id: legislator for legislator in session.scalars(select(Legislator)).all()
+        }
+        logger.info("Found %d existing legislators in DB", len(existing_legislators))
+
+        total_inserted = 0
+        total_updated = 0
+        for entry in legislators_data:
+            bioguide_id = entry.get("id", {}).get("bioguide")
+            if not bioguide_id:
+                continue
+
+            fields = _parse_legislator(entry)
+            if existing := existing_legislators.get(bioguide_id):
+                changed = False
+                for field, value in fields.items():
+                    if value is not None and getattr(existing, field) != value:
+                        setattr(existing, field, value)
+                        changed = True
+                if changed:
+                    total_updated += 1
+            else:
+                session.add(Legislator(bioguide_id=bioguide_id, **fields))
+                total_inserted += 1
+
+        session.commit()
+    logger.info("Inserted %d new legislators, updated %d existing", total_inserted, total_updated)
+
+
+def _load_legislators_yaml(legislators_dir: Path) -> list[dict]:
+    """Load and combine legislators-current.yaml and legislators-historical.yaml."""
+    legislators: list[dict] = []
+    for filename in ("legislators-current.yaml", "legislators-historical.yaml"):
+        path = legislators_dir / filename
+        if not path.exists():
+            logger.warning("Legislators file not found: %s", path)
+            continue
+        with path.open() as file:
+            data = yaml.safe_load(file)
+            if isinstance(data, list):
+                legislators.extend(data)
+    return legislators
+
+
+def _parse_legislator(entry: dict) -> dict:
+    """Extract Legislator fields from a congress-legislators YAML entry."""
+    ids = entry.get("id", {})
+    name = entry.get("name", {})
+    bio = entry.get("bio", {})
+    terms = entry.get("terms", [])
+    latest_term = terms[-1] if terms else {}
+
+    fec_ids = ids.get("fec")
+    fec_ids_joined = ",".join(fec_ids) if isinstance(fec_ids, list) else fec_ids
+
+    chamber = latest_term.get("type")
+    chamber_normalized = {"rep": "House", "sen": "Senate"}.get(chamber, chamber)
+
+    return {
+        "thomas_id": ids.get("thomas"),
+        "lis_id": ids.get("lis"),
+        "govtrack_id": ids.get("govtrack"),
+        "opensecrets_id": ids.get("opensecrets"),
+        "fec_ids": fec_ids_joined,
+        "first_name": name.get("first"),
+        "last_name": name.get("last"),
+        "official_full_name": name.get("official_full"),
+        "nickname": name.get("nickname"),
+        "birthday": bio.get("birthday"),
+        "gender": bio.get("gender"),
+        "current_party": latest_term.get("party"),
+        "current_state": latest_term.get("state"),
+        "current_district": latest_term.get("district"),
+        "current_chamber": chamber_normalized,
+    }
+
+
+# ---------------------------------------------------------------------------
+# Social Media — loaded from legislators-social-media.yaml
+# ---------------------------------------------------------------------------
+
+SOCIAL_MEDIA_PLATFORMS = {
+    "twitter": "https://twitter.com/{account}",
+    "facebook": "https://facebook.com/{account}",
+    "youtube": "https://youtube.com/{account}",
+    "instagram": "https://instagram.com/{account}",
+    "mastodon": None,
+}
+
+
+def ingest_social_media(engine: Engine, legislators_dir: Path) -> None:
+    """Load social media accounts from legislators-social-media.yaml."""
+    social_media_path = legislators_dir / "legislators-social-media.yaml"
+    if not social_media_path.exists():
+        logger.warning("Social media file not found: %s", social_media_path)
+        return
+
+    with social_media_path.open() as file:
+        social_media_data = yaml.safe_load(file)
+
+    if not isinstance(social_media_data, list):
+        logger.warning("Unexpected format in %s", social_media_path)
+        return
+
+    logger.info("Loaded %d entries from legislators-social-media.yaml", len(social_media_data))
+
+    with Session(engine) as session:
+        legislator_map = _build_legislator_map(session)
+        existing_accounts = {
+            (account.legislator_id, account.platform)
+            for account in session.scalars(select(LegislatorSocialMedia)).all()
+        }
+        logger.info("Found %d existing social media accounts in DB", len(existing_accounts))
+
+        total_inserted = 0
+        total_updated = 0
+        for entry in social_media_data:
+            bioguide_id = entry.get("id", {}).get("bioguide")
+            if not bioguide_id:
+                continue
+
+            legislator_id = legislator_map.get(bioguide_id)
+            if legislator_id is None:
+                continue
+
+            social = entry.get("social", {})
+            for platform, url_template in SOCIAL_MEDIA_PLATFORMS.items():
+                account_name = social.get(platform)
+                if not account_name:
+                    continue
+
+                url = url_template.format(account=account_name) if url_template else None
+
+                if (legislator_id, platform) in existing_accounts:
+                    total_updated += 1
+                else:
+                    session.add(
+                        LegislatorSocialMedia(
+                            legislator_id=legislator_id,
+                            platform=platform,
+                            account_name=str(account_name),
+                            url=url,
+                            source="https://github.com/unitedstates/congress-legislators",
+                        )
+                    )
+                    existing_accounts.add((legislator_id, platform))
+                    total_inserted += 1
+
+        session.commit()
+    logger.info("Inserted %d new social media accounts, updated %d existing", total_inserted, total_updated)
+
+
+def _iter_voters(position_group: object) -> Iterator[dict]:
+    """Yield voter dicts from a vote position group (handles list, single dict, or string)."""
+    if isinstance(position_group, dict):
+        yield position_group
+    elif isinstance(position_group, list):
+        for voter in position_group:
+            if isinstance(voter, dict):
+                yield voter
+
+
+# ---------------------------------------------------------------------------
+# Bills
+# ---------------------------------------------------------------------------
+
+
+def ingest_bills(engine: Engine, congress_dirs: list[Path]) -> None:
+    """Load bill data.json files."""
+    with Session(engine) as session:
+        existing_bills = {(bill.congress, bill.bill_type, bill.number) for bill in session.scalars(select(Bill)).all()}
+        logger.info("Found %d existing bills in DB", len(existing_bills))
+
+        total_inserted = 0
+        batch: list[Bill] = []
+        for congress_dir in congress_dirs:
+            bills_dir = congress_dir / "bills"
+            if not bills_dir.is_dir():
+                continue
+            logger.info("Scanning bills from %s", congress_dir.name)
+            for bill_file in bills_dir.rglob("data.json"):
+                data = _read_json(bill_file)
+                if data is None:
+                    continue
+                bill = _parse_bill(data, existing_bills)
+                if bill is not None:
+                    batch.append(bill)
+                    if len(batch) >= BATCH_SIZE:
+                        total_inserted += _flush_batch(session, batch, "bills")
+
+        total_inserted += _flush_batch(session, batch, "bills")
+    logger.info("Inserted %d new bills total", total_inserted)
+
+
+def _parse_bill(data: dict, existing_bills: set[tuple[int, str, int]]) -> Bill | None:
+    """Parse a bill data.json dict into a Bill ORM object, skipping existing."""
+    raw_congress = data.get("congress")
+    bill_type = data.get("bill_type")
+    raw_number = data.get("number")
+    if raw_congress is None or bill_type is None or raw_number is None:
+        return None
+    congress = int(raw_congress)
+    number = int(raw_number)
+    if (congress, bill_type, number) in existing_bills:
+        return None
+
+    sponsor_bioguide = None
+    sponsor = data.get("sponsor")
+    if sponsor:
+        sponsor_bioguide = sponsor.get("bioguide_id")
+
+    return Bill(
+        congress=congress,
+        bill_type=bill_type,
+        number=number,
+        title=data.get("short_title") or data.get("official_title"),
+        title_short=data.get("short_title"),
+        official_title=data.get("official_title"),
+        status=data.get("status"),
+        status_at=data.get("status_at"),
+        sponsor_bioguide_id=sponsor_bioguide,
+        subjects_top_term=data.get("subjects_top_term"),
+    )
+
+
+# ---------------------------------------------------------------------------
+# Votes (and vote records)
+# ---------------------------------------------------------------------------
+
+
+def ingest_votes(engine: Engine, congress_dirs: list[Path]) -> None:
+    """Load vote data.json files with their vote records."""
+    with Session(engine) as session:
+        legislator_map = _build_legislator_map(session)
+        logger.info("Loaded %d legislators into lookup map", len(legislator_map))
+        bill_map = _build_bill_map(session)
+        logger.info("Loaded %d bills into lookup map", len(bill_map))
+        existing_votes = {
+            (vote.congress, vote.chamber, vote.session, vote.number) for vote in session.scalars(select(Vote)).all()
+        }
+        logger.info("Found %d existing votes in DB", len(existing_votes))
+
+        total_inserted = 0
+        batch: list[Vote] = []
+        for congress_dir in congress_dirs:
+            votes_dir = congress_dir / "votes"
+            if not votes_dir.is_dir():
+                continue
+            logger.info("Scanning votes from %s", congress_dir.name)
+            for vote_file in votes_dir.rglob("data.json"):
+                data = _read_json(vote_file)
+                if data is None:
+                    continue
+                vote = _parse_vote(data, legislator_map, bill_map, existing_votes)
+                if vote is not None:
+                    batch.append(vote)
+                    if len(batch) >= BATCH_SIZE:
+                        total_inserted += _flush_batch(session, batch, "votes")
+
+        total_inserted += _flush_batch(session, batch, "votes")
+    logger.info("Inserted %d new votes total", total_inserted)
+
+
+def _build_legislator_map(session: Session) -> dict[str, int]:
+    """Build a mapping of bioguide_id -> legislator.id."""
+    return {legislator.bioguide_id: legislator.id for legislator in session.scalars(select(Legislator)).all()}
+
+
+def _build_bill_map(session: Session) -> dict[tuple[int, str, int], int]:
+    """Build a mapping of (congress, bill_type, number) -> bill.id."""
+    return {(bill.congress, bill.bill_type, bill.number): bill.id for bill in session.scalars(select(Bill)).all()}
+
+
+def _parse_vote(
+    data: dict,
+    legislator_map: dict[str, int],
+    bill_map: dict[tuple[int, str, int], int],
+    existing_votes: set[tuple[int, str, int, int]],
+) -> Vote | None:
+    """Parse a vote data.json dict into a Vote ORM object with records."""
+    raw_congress = data.get("congress")
+    chamber = data.get("chamber")
+    raw_number = data.get("number")
+    vote_date = data.get("date")
+    if raw_congress is None or chamber is None or raw_number is None or vote_date is None:
+        return None
+
+    raw_session = data.get("session")
+    if raw_session is None:
+        return None
+
+    congress = int(raw_congress)
+    number = int(raw_number)
+    session_number = int(raw_session)
+
+    # Normalize chamber from "h"/"s" to "House"/"Senate"
+    chamber_normalized = {"h": "House", "s": "Senate"}.get(chamber, chamber)
+
+    if (congress, chamber_normalized, session_number, number) in existing_votes:
+        return None
+
+    # Resolve linked bill
+    bill_id = None
+    bill_ref = data.get("bill")
+    if bill_ref:
+        bill_key = (
+            int(bill_ref.get("congress", congress)),
+            bill_ref.get("type"),
+            int(bill_ref.get("number", 0)),
+        )
+        bill_id = bill_map.get(bill_key)
+
+    raw_votes = data.get("votes", {})
+    vote_counts = _count_votes(raw_votes)
+    vote_records = _build_vote_records(raw_votes, legislator_map)
+
+    return Vote(
+        congress=congress,
+        chamber=chamber_normalized,
+        session=session_number,
+        number=number,
+        vote_type=data.get("type"),
+        question=data.get("question"),
+        result=data.get("result"),
+        result_text=data.get("result_text"),
+        vote_date=vote_date[:10] if isinstance(vote_date, str) else vote_date,
+        bill_id=bill_id,
+        vote_records=vote_records,
+        **vote_counts,
+    )
+
+
+def _count_votes(raw_votes: dict) -> dict[str, int]:
+    """Count voters per position category, correctly handling dict and list formats."""
+    yea_count = 0
+    nay_count = 0
+    not_voting_count = 0
+    present_count = 0
+
+    for position, position_group in raw_votes.items():
+        voter_count = sum(1 for _ in _iter_voters(position_group))
+        if position in ("Yea", "Aye"):
+            yea_count += voter_count
+        elif position in ("Nay", "No"):
+            nay_count += voter_count
+        elif position == "Not Voting":
+            not_voting_count += voter_count
+        elif position == "Present":
+            present_count += voter_count
+
+    return {
+        "yea_count": yea_count,
+        "nay_count": nay_count,
+        "not_voting_count": not_voting_count,
+        "present_count": present_count,
+    }
+
+
+def _build_vote_records(raw_votes: dict, legislator_map: dict[str, int]) -> list[VoteRecord]:
+    """Build VoteRecord objects from raw vote data."""
+    records: list[VoteRecord] = []
+    for position, position_group in raw_votes.items():
+        for voter in _iter_voters(position_group):
+            bioguide_id = voter.get("id")
+            if not bioguide_id:
+                continue
+            legislator_id = legislator_map.get(bioguide_id)
+            if legislator_id is None:
+                continue
+            records.append(
+                VoteRecord(
+                    legislator_id=legislator_id,
+                    position=position,
+                )
+            )
+    return records
+
+
+# ---------------------------------------------------------------------------
+# Bill Text
+# ---------------------------------------------------------------------------
+
+
+def ingest_bill_text(engine: Engine, congress_dirs: list[Path]) -> None:
+    """Load bill text from text-versions directories."""
+    with Session(engine) as session:
+        bill_map = _build_bill_map(session)
+        logger.info("Loaded %d bills into lookup map", len(bill_map))
+        existing_bill_texts = {
+            (bill_text.bill_id, bill_text.version_code) for bill_text in session.scalars(select(BillText)).all()
+        }
+        logger.info("Found %d existing bill text versions in DB", len(existing_bill_texts))
+
+        total_inserted = 0
+        batch: list[BillText] = []
+        for congress_dir in congress_dirs:
+            logger.info("Scanning bill texts from %s", congress_dir.name)
+            for bill_text in _iter_bill_texts(congress_dir, bill_map, existing_bill_texts):
+                batch.append(bill_text)
+                if len(batch) >= BATCH_SIZE:
+                    total_inserted += _flush_batch(session, batch, "bill texts")
+
+        total_inserted += _flush_batch(session, batch, "bill texts")
+    logger.info("Inserted %d new bill text versions total", total_inserted)
+
+
+def _iter_bill_texts(
+    congress_dir: Path,
+    bill_map: dict[tuple[int, str, int], int],
+    existing_bill_texts: set[tuple[int, str]],
+) -> Iterator[BillText]:
+    """Yield BillText objects for a single congress directory, skipping existing."""
+    bills_dir = congress_dir / "bills"
+    if not bills_dir.is_dir():
+        return
+
+    for bill_dir in bills_dir.rglob("text-versions"):
+        if not bill_dir.is_dir():
+            continue
+        bill_key = _bill_key_from_dir(bill_dir.parent, congress_dir)
+        if bill_key is None:
+            continue
+        bill_id = bill_map.get(bill_key)
+        if bill_id is None:
+            continue
+
+        for version_dir in sorted(bill_dir.iterdir()):
+            if not version_dir.is_dir():
+                continue
+            if (bill_id, version_dir.name) in existing_bill_texts:
+                continue
+            text_content = _read_bill_text(version_dir)
+            version_data = _read_json(version_dir / "data.json")
+            yield BillText(
+                bill_id=bill_id,
+                version_code=version_dir.name,
+                version_name=version_data.get("version_name") if version_data else None,
+                date=version_data.get("issued_on") if version_data else None,
+                text_content=text_content,
+            )
+
+
+def _bill_key_from_dir(bill_dir: Path, congress_dir: Path) -> tuple[int, str, int] | None:
+    """Extract (congress, bill_type, number) from directory structure."""
+    congress = int(congress_dir.name)
+    bill_type = bill_dir.parent.name
+    name = bill_dir.name
+    # Directory name is like "hr3590" — strip the type prefix to get the number
+    number_str = name[len(bill_type) :]
+    if not number_str.isdigit():
+        return None
+    return (congress, bill_type, int(number_str))
+
+
+def _read_bill_text(version_dir: Path) -> str | None:
+    """Read bill text from a version directory, preferring .txt over .xml."""
+    for extension in ("txt", "htm", "html", "xml"):
+        candidates = list(version_dir.glob(f"document.{extension}"))
+        if not candidates:
+            candidates = list(version_dir.glob(f"*.{extension}"))
+        if candidates:
+            try:
+                return candidates[0].read_text(encoding="utf-8")
+            except Exception:
+                logger.exception("Failed to read %s", candidates[0])
+    return None
+
+
+# ---------------------------------------------------------------------------
+# Helpers
+# ---------------------------------------------------------------------------
+
+
+def _read_json(path: Path) -> dict | None:
+    """Read and parse a JSON file, returning None on failure."""
+    try:
+        return orjson.loads(path.read_bytes())
+    except FileNotFoundError:
+        return None
+    except Exception:
+        logger.exception("Failed to parse %s", path)
+        return None
+
+
+if __name__ == "__main__":
+    app()

python/data_science/ingest_posts.py

@@ -0,0 +1,247 @@
+"""Ingestion pipeline for loading JSONL post files into the weekly-partitioned posts table.
+
+Usage:
+    ingest-posts /path/to/files/
+    ingest-posts /path/to/single_file.jsonl
+    ingest-posts /data/dir/ --workers 4 --batch-size 5000
+"""
+
+from __future__ import annotations
+
+import logging
+from datetime import UTC, datetime
+from pathlib import Path  # noqa: TC003 this is needed for typer
+from typing import TYPE_CHECKING, Annotated
+
+import orjson
+import psycopg
+import typer
+
+from python.common import configure_logger
+from python.orm.common import get_connection_info
+from python.parallelize import parallelize_process
+
+if TYPE_CHECKING:
+    from collections.abc import Iterator
+
+logger = logging.getLogger(__name__)
+
+
+app = typer.Typer(help="Ingest JSONL post files into the partitioned posts table.")
+
+
+@app.command()
+def main(
+    path: Annotated[Path, typer.Argument(help="Directory containing JSONL files, or a single JSONL file")],
+    batch_size: Annotated[int, typer.Option(help="Rows per INSERT batch")] = 10000,
+    workers: Annotated[int, typer.Option(help="Parallel workers for multi-file ingestion")] = 4,
+    pattern: Annotated[str, typer.Option(help="Glob pattern for JSONL files")] = "*.jsonl",
+) -> None:
+    """Ingest JSONL post files into the weekly-partitioned posts table."""
+    configure_logger(level="INFO")
+
+    logger.info("starting ingest-posts")
+    logger.info("path=%s batch_size=%d workers=%d pattern=%s", path, batch_size, workers, pattern)
+    if path.is_file():
+        ingest_file(path, batch_size=batch_size)
+    elif path.is_dir():
+        ingest_directory(path, batch_size=batch_size, max_workers=workers, pattern=pattern)
+    else:
+        typer.echo(f"Path does not exist: {path}", err=True)
+        raise typer.Exit(code=1)
+
+    logger.info("ingest-posts done")
+
+
+def ingest_directory(
+    directory: Path,
+    *,
+    batch_size: int,
+    max_workers: int,
+    pattern: str = "*.jsonl",
+) -> None:
+    """Ingest all JSONL files in a directory using parallel workers."""
+    files = sorted(directory.glob(pattern))
+    if not files:
+        logger.warning("No JSONL files found in %s", directory)
+        return
+
+    logger.info("Found %d JSONL files to ingest", len(files))
+
+    kwargs_list = [{"path": fp, "batch_size": batch_size} for fp in files]
+    parallelize_process(ingest_file, kwargs_list, max_workers=max_workers)
+
+
+SCHEMA = "main"
+
+COLUMNS = (
+    "post_id",
+    "user_id",
+    "instance",
+    "date",
+    "text",
+    "langs",
+    "like_count",
+    "reply_count",
+    "repost_count",
+    "reply_to",
+    "replied_author",
+    "thread_root",
+    "thread_root_author",
+    "repost_from",
+    "reposted_author",
+    "quotes",
+    "quoted_author",
+    "labels",
+    "sent_label",
+    "sent_score",
+)
+
+INSERT_FROM_STAGING = f"""
+    INSERT INTO {SCHEMA}.posts ({", ".join(COLUMNS)})
+    SELECT {", ".join(COLUMNS)} FROM pg_temp.staging
+    ON CONFLICT (post_id, date) DO NOTHING
+"""  # noqa: S608
+
+FAILED_INSERT = f"""
+    INSERT INTO {SCHEMA}.failed_ingestion (raw_line, error)
+    VALUES (%(raw_line)s, %(error)s)
+"""  # noqa: S608
+
+
+def get_psycopg_connection() -> psycopg.Connection:
+    """Create a raw psycopg3 connection from environment variables."""
+    database, host, port, username, password = get_connection_info("DATA_SCIENCE_DEV")
+    return psycopg.connect(
+        dbname=database,
+        host=host,
+        port=int(port),
+        user=username,
+        password=password,
+        autocommit=False,
+    )
+
+
+def ingest_file(path: Path, *, batch_size: int) -> None:
+    """Ingest a single JSONL file into the posts table."""
+    log_trigger = max(100_000 // batch_size, 1)
+    failed_lines: list[dict] = []
+    try:
+        with get_psycopg_connection() as connection:
+            for index, batch in enumerate(read_jsonl_batches(path, batch_size, failed_lines), 1):
+                ingest_batch(connection, batch)
+                if index % log_trigger == 0:
+                    logger.info("Ingested %d batches (%d rows) from %s", index, index * batch_size, path)
+
+            if failed_lines:
+                logger.warning("Recording %d malformed lines from %s", len(failed_lines), path.name)
+                with connection.cursor() as cursor:
+                    cursor.executemany(FAILED_INSERT, failed_lines)
+                connection.commit()
+    except Exception:
+        logger.exception("Failed to ingest file: %s", path)
+        raise
+
+
+def ingest_batch(connection: psycopg.Connection, batch: list[dict]) -> None:
+    """COPY batch into a temp staging table, then INSERT ... ON CONFLICT into posts."""
+    if not batch:
+        return
+
+    try:
+        with connection.cursor() as cursor:
+            cursor.execute(f"""
+                CREATE TEMP TABLE IF NOT EXISTS staging
+                (LIKE {SCHEMA}.posts INCLUDING DEFAULTS)
+                ON COMMIT DELETE ROWS
+            """)
+            cursor.execute("TRUNCATE pg_temp.staging")
+
+            with cursor.copy(f"COPY pg_temp.staging ({', '.join(COLUMNS)}) FROM STDIN") as copy:
+                for row in batch:
+                    copy.write_row(tuple(row.get(column) for column in COLUMNS))
+
+            cursor.execute(INSERT_FROM_STAGING)
+        connection.commit()
+    except Exception as error:
+        connection.rollback()
+
+        if len(batch) == 1:
+            logger.exception("Skipping bad row post_id=%s", batch[0].get("post_id"))
+            with connection.cursor() as cursor:
+                cursor.execute(
+                    FAILED_INSERT,
+                    {
+                        "raw_line": orjson.dumps(batch[0], default=str).decode(),
+                        "error": str(error),
+                    },
+                )
+            connection.commit()
+            return
+
+        midpoint = len(batch) // 2
+        ingest_batch(connection, batch[:midpoint])
+        ingest_batch(connection, batch[midpoint:])
+
+
+def read_jsonl_batches(file_path: Path, batch_size: int, failed_lines: list[dict]) -> Iterator[list[dict]]:
+    """Stream a JSONL file and yield batches of transformed rows."""
+    batch: list[dict] = []
+    with file_path.open("r", encoding="utf-8") as handle:
+        for raw_line in handle:
+            line = raw_line.strip()
+            if not line:
+                continue
+            batch.extend(parse_line(line, file_path, failed_lines))
+            if len(batch) >= batch_size:
+                yield batch
+                batch = []
+    if batch:
+        yield batch
+
+
+def parse_line(line: str, file_path: Path, failed_lines: list[dict]) -> Iterator[dict]:
+    """Parse a JSONL line, handling concatenated JSON objects."""
+    try:
+        yield transform_row(orjson.loads(line))
+    except orjson.JSONDecodeError:
+        if "}{" not in line:
+            logger.warning("Skipping malformed line in %s: %s", file_path.name, line[:120])
+            failed_lines.append({"raw_line": line, "error": "malformed JSON"})
+            return
+        fragments = line.replace("}{", "}\n{").split("\n")
+        for fragment in fragments:
+            try:
+                yield transform_row(orjson.loads(fragment))
+            except (orjson.JSONDecodeError, KeyError, ValueError) as error:
+                logger.warning("Skipping malformed fragment in %s: %s", file_path.name, fragment[:120])
+                failed_lines.append({"raw_line": fragment, "error": str(error)})
+    except Exception as error:
+        logger.exception("Skipping bad row in %s: %s", file_path.name, line[:120])
+        failed_lines.append({"raw_line": line, "error": str(error)})
+
+
+def transform_row(raw: dict) -> dict:
+    """Transform a raw JSONL row into a dict matching the Posts table columns."""
+    raw["date"] = parse_date(raw["date"])
+    if raw.get("langs") is not None:
+        raw["langs"] = orjson.dumps(raw["langs"])
+    if raw.get("text") is not None:
+        raw["text"] = raw["text"].replace("\x00", "")
+    return raw
+
+
+def parse_date(raw_date: int) -> datetime:
+    """Parse compact YYYYMMDDHHmm integer into a naive datetime (input is UTC by spec)."""
+    return datetime(
+        raw_date // 100000000,
+        (raw_date // 1000000) % 100,
+        (raw_date // 10000) % 100,
+        (raw_date // 100) % 100,
+        raw_date % 100,
+        tzinfo=UTC,
+    )
+
+
+if __name__ == "__main__":
+    app()

python/database_cli.py

@@ -90,6 +90,13 @@ DATABASES: dict[str, DatabaseConfig] = {
         base_class_name="SignalBotBase",
         models_module="python.orm.signal_bot.models",
     ),
+    "data_science_dev": DatabaseConfig(
+        env_prefix="DATA_SCIENCE_DEV",
+        version_location="python/alembic/data_science_dev/versions",
+        base_module="python.orm.data_science_dev.base",
+        base_class_name="DataScienceDevBase",
+        models_module="python.orm.data_science_dev.models",
+    ),
 }
 
 

python/orm/__init__.py

@@ -1,10 +1,12 @@
 """ORM package exports."""
 
+from python.orm.data_science_dev.base import DataScienceDevBase
 from python.orm.richie.base import RichieBase
 from python.orm.signal_bot.base import SignalBotBase
 from python.orm.van_inventory.base import VanInventoryBase
 
 __all__ = [
+    "DataScienceDevBase",
     "RichieBase",
     "SignalBotBase",
     "VanInventoryBase",

python/orm/data_science_dev/__init__.py

@@ -0,0 +1,11 @@
+"""Data science dev database ORM exports."""
+
+from __future__ import annotations
+
+from python.orm.data_science_dev.base import DataScienceDevBase, DataScienceDevTableBase, DataScienceDevTableBaseBig
+
+__all__ = [
+    "DataScienceDevBase",
+    "DataScienceDevTableBase",
+    "DataScienceDevTableBaseBig",
+]

python/orm/data_science_dev/base.py

@@ -0,0 +1,52 @@
+"""Data science dev database ORM base."""
+
+from __future__ import annotations
+
+from datetime import datetime
+
+from sqlalchemy import BigInteger, DateTime, MetaData, func
+from sqlalchemy.ext.declarative import AbstractConcreteBase
+from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
+
+from python.orm.common import NAMING_CONVENTION
+
+
+class DataScienceDevBase(DeclarativeBase):
+    """Base class for data_science_dev database ORM models."""
+
+    schema_name = "main"
+
+    metadata = MetaData(
+        schema=schema_name,
+        naming_convention=NAMING_CONVENTION,
+    )
+
+
+class _TableMixin:
+    """Shared timestamp columns for all table bases."""
+
+    created: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True),
+        server_default=func.now(),
+    )
+    updated: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True),
+        server_default=func.now(),
+        onupdate=func.now(),
+    )
+
+
+class DataScienceDevTableBase(_TableMixin, AbstractConcreteBase, DataScienceDevBase):
+    """Table with Integer primary key."""
+
+    __abstract__ = True
+
+    id: Mapped[int] = mapped_column(primary_key=True)
+
+
+class DataScienceDevTableBaseBig(_TableMixin, AbstractConcreteBase, DataScienceDevBase):
+    """Table with BigInteger primary key."""
+
+    __abstract__ = True
+
+    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)

python/orm/data_science_dev/congress/__init__.py

@@ -0,0 +1,14 @@
+"""init."""
+
+from python.orm.data_science_dev.congress.bill import Bill, BillText
+from python.orm.data_science_dev.congress.legislator import Legislator, LegislatorSocialMedia
+from python.orm.data_science_dev.congress.vote import Vote, VoteRecord
+
+__all__ = [
+    "Bill",
+    "BillText",
+    "Legislator",
+    "LegislatorSocialMedia",
+    "Vote",
+    "VoteRecord",
+]

python/orm/data_science_dev/congress/bill.py

@@ -0,0 +1,66 @@
+"""Bill model - legislation introduced in Congress."""
+
+from __future__ import annotations
+
+from datetime import date
+from typing import TYPE_CHECKING
+
+from sqlalchemy import ForeignKey, Index, UniqueConstraint
+from sqlalchemy.orm import Mapped, mapped_column, relationship
+
+from python.orm.data_science_dev.base import DataScienceDevTableBase
+
+if TYPE_CHECKING:
+    from python.orm.data_science_dev.congress.vote import Vote
+
+
+class Bill(DataScienceDevTableBase):
+    """Legislation with congress number, type, titles, status, and sponsor."""
+
+    __tablename__ = "bill"
+
+    congress: Mapped[int]
+    bill_type: Mapped[str]
+    number: Mapped[int]
+
+    title: Mapped[str | None]
+    title_short: Mapped[str | None]
+    official_title: Mapped[str | None]
+
+    status: Mapped[str | None]
+    status_at: Mapped[date | None]
+
+    sponsor_bioguide_id: Mapped[str | None]
+
+    subjects_top_term: Mapped[str | None]
+
+    votes: Mapped[list[Vote]] = relationship(
+        "Vote",
+        back_populates="bill",
+    )
+    bill_texts: Mapped[list[BillText]] = relationship(
+        "BillText",
+        back_populates="bill",
+        cascade="all, delete-orphan",
+    )
+
+    __table_args__ = (
+        UniqueConstraint("congress", "bill_type", "number", name="uq_bill_congress_type_number"),
+        Index("ix_bill_congress", "congress"),
+    )
+
+
+class BillText(DataScienceDevTableBase):
+    """Stores different text versions of a bill (introduced, enrolled, etc.)."""
+
+    __tablename__ = "bill_text"
+
+    bill_id: Mapped[int] = mapped_column(ForeignKey("main.bill.id", ondelete="CASCADE"))
+    version_code: Mapped[str]
+    version_name: Mapped[str | None]
+    text_content: Mapped[str | None]
+    date: Mapped[date | None]
+
+    bill: Mapped[Bill] = relationship("Bill", back_populates="bill_texts")
+
+    __table_args__ = (UniqueConstraint("bill_id", "version_code", name="uq_bill_text_bill_id_version_code"),)

python/orm/data_science_dev/congress/legislator.py

@@ -0,0 +1,66 @@
+"""Legislator model - members of Congress."""
+
+from __future__ import annotations
+
+from datetime import date
+from typing import TYPE_CHECKING
+
+from sqlalchemy import ForeignKey, Text
+from sqlalchemy.orm import Mapped, mapped_column, relationship
+
+from python.orm.data_science_dev.base import DataScienceDevTableBase
+
+if TYPE_CHECKING:
+    from python.orm.data_science_dev.congress.vote import VoteRecord
+
+
+class Legislator(DataScienceDevTableBase):
+    """Members of Congress with identification and current term info."""
+
+    __tablename__ = "legislator"
+
+    bioguide_id: Mapped[str] = mapped_column(Text, unique=True, index=True)
+
+    thomas_id: Mapped[str | None]
+    lis_id: Mapped[str | None]
+    govtrack_id: Mapped[int | None]
+    opensecrets_id: Mapped[str | None]
+    fec_ids: Mapped[str | None]
+
+    first_name: Mapped[str]
+    last_name: Mapped[str]
+    official_full_name: Mapped[str | None]
+    nickname: Mapped[str | None]
+
+    birthday: Mapped[date | None]
+    gender: Mapped[str | None]
+
+    current_party: Mapped[str | None]
+    current_state: Mapped[str | None]
+    current_district: Mapped[int | None]
+    current_chamber: Mapped[str | None]
+
+    social_media_accounts: Mapped[list[LegislatorSocialMedia]] = relationship(
+        "LegislatorSocialMedia",
+        back_populates="legislator",
+        cascade="all, delete-orphan",
+    )
+    vote_records: Mapped[list[VoteRecord]] = relationship(
+        "VoteRecord",
+        back_populates="legislator",
+        cascade="all, delete-orphan",
+    )
+
+
+class LegislatorSocialMedia(DataScienceDevTableBase):
+    """Social media account linked to a legislator."""
+
+    __tablename__ = "legislator_social_media"
+
+    legislator_id: Mapped[int] = mapped_column(ForeignKey("main.legislator.id"))
+    platform: Mapped[str]
+    account_name: Mapped[str]
+    url: Mapped[str | None]
+    source: Mapped[str]
+
+    legislator: Mapped[Legislator] = relationship(back_populates="social_media_accounts")

python/orm/data_science_dev/congress/vote.py

@@ -0,0 +1,79 @@
+"""Vote model - roll call votes in Congress."""
+
+from __future__ import annotations
+
+from datetime import date
+from typing import TYPE_CHECKING
+
+from sqlalchemy import ForeignKey, Index, UniqueConstraint
+from sqlalchemy.orm import Mapped, mapped_column, relationship
+
+from python.orm.data_science_dev.base import DataScienceDevBase, DataScienceDevTableBase
+
+if TYPE_CHECKING:
+    from python.orm.data_science_dev.congress.bill import Bill
+    from python.orm.data_science_dev.congress.legislator import Legislator
+    from python.orm.data_science_dev.congress.vote import Vote
+
+
+class VoteRecord(DataScienceDevBase):
+    """Links a vote to a legislator with their position (Yea, Nay, etc.)."""
+
+    __tablename__ = "vote_record"
+
+    vote_id: Mapped[int] = mapped_column(
+        ForeignKey("main.vote.id", ondelete="CASCADE"),
+        primary_key=True,
+    )
+    legislator_id: Mapped[int] = mapped_column(
+        ForeignKey("main.legislator.id", ondelete="CASCADE"),
+        primary_key=True,
+    )
+    position: Mapped[str]
+
+    vote: Mapped[Vote] = relationship("Vote", back_populates="vote_records")
+    legislator: Mapped[Legislator] = relationship("Legislator", back_populates="vote_records")
+
+
+class Vote(DataScienceDevTableBase):
+    """Roll call votes with counts and optional bill linkage."""
+
+    __tablename__ = "vote"
+
+    congress: Mapped[int]
+    chamber: Mapped[str]
+    session: Mapped[int]
+    number: Mapped[int]
+
+    vote_type: Mapped[str | None]
+    question: Mapped[str | None]
+    result: Mapped[str | None]
+    result_text: Mapped[str | None]
+
+    vote_date: Mapped[date]
+
+    yea_count: Mapped[int | None]
+    nay_count: Mapped[int | None]
+    not_voting_count: Mapped[int | None]
+    present_count: Mapped[int | None]
+
+    bill_id: Mapped[int | None] = mapped_column(ForeignKey("main.bill.id"))
+
+    bill: Mapped[Bill | None] = relationship("Bill", back_populates="votes")
+    vote_records: Mapped[list[VoteRecord]] = relationship(
+        "VoteRecord",
+        back_populates="vote",
+        cascade="all, delete-orphan",
+    )
+
+    __table_args__ = (
+        UniqueConstraint(
+            "congress",
+            "chamber",
+            "session",
+            "number",
+            name="uq_vote_congress_chamber_session_number",
+        ),
+        Index("ix_vote_date", "vote_date"),
+        Index("ix_vote_congress_chamber", "congress", "chamber"),
+    )

python/orm/data_science_dev/models.py

@@ -0,0 +1,16 @@
+"""Data science dev database ORM models."""
+
+from __future__ import annotations
+
+from python.orm.data_science_dev.congress import Bill, BillText, Legislator, Vote, VoteRecord
+from python.orm.data_science_dev.posts import partitions  # noqa: F401 — registers partition classes in metadata
+from python.orm.data_science_dev.posts.tables import Posts
+
+__all__ = [
+    "Bill",
+    "BillText",
+    "Legislator",
+    "Posts",
+    "Vote",
+    "VoteRecord",
+]

python/orm/data_science_dev/posts/__init__.py

@@ -0,0 +1,11 @@
+"""Posts module — weekly-partitioned posts table and partition ORM models."""
+
+from __future__ import annotations
+
+from python.orm.data_science_dev.posts.failed_ingestion import FailedIngestion
+from python.orm.data_science_dev.posts.tables import Posts
+
+__all__ = [
+    "FailedIngestion",
+    "Posts",
+]

python/orm/data_science_dev/posts/columns.py

@@ -0,0 +1,33 @@
+"""Shared column definitions for the posts partitioned table family."""
+
+from __future__ import annotations
+
+from datetime import datetime
+
+from sqlalchemy import BigInteger, SmallInteger, Text
+from sqlalchemy.orm import Mapped, mapped_column
+
+
+class PostsColumns:
+    """Mixin providing all posts columns. Used by both the parent table and partitions."""
+
+    post_id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
+    user_id: Mapped[int] = mapped_column(BigInteger)
+    instance: Mapped[str]
+    date: Mapped[datetime] = mapped_column(primary_key=True)
+    text: Mapped[str] = mapped_column(Text)
+    langs: Mapped[str | None]
+    like_count: Mapped[int]
+    reply_count: Mapped[int]
+    repost_count: Mapped[int]
+    reply_to: Mapped[int | None] = mapped_column(BigInteger)
+    replied_author: Mapped[int | None] = mapped_column(BigInteger)
+    thread_root: Mapped[int | None] = mapped_column(BigInteger)
+    thread_root_author: Mapped[int | None] = mapped_column(BigInteger)
+    repost_from: Mapped[int | None] = mapped_column(BigInteger)
+    reposted_author: Mapped[int | None] = mapped_column(BigInteger)
+    quotes: Mapped[int | None] = mapped_column(BigInteger)
+    quoted_author: Mapped[int | None] = mapped_column(BigInteger)
+    labels: Mapped[str | None]
+    sent_label: Mapped[int | None] = mapped_column(SmallInteger)
+    sent_score: Mapped[float | None]

python/orm/data_science_dev/posts/failed_ingestion.py

@@ -0,0 +1,17 @@
+"""Table for storing JSONL lines that failed during post ingestion."""
+
+from __future__ import annotations
+
+from sqlalchemy import Text
+from sqlalchemy.orm import Mapped, mapped_column
+
+from python.orm.data_science_dev.base import DataScienceDevTableBase
+
+
+class FailedIngestion(DataScienceDevTableBase):
+    """Stores raw JSONL lines and their error messages when ingestion fails."""
+
+    __tablename__ = "failed_ingestion"
+
+    raw_line: Mapped[str] = mapped_column(Text)
+    error: Mapped[str] = mapped_column(Text)

python/orm/data_science_dev/posts/partitions.py

@@ -0,0 +1,71 @@
+"""Dynamically generated ORM classes for each weekly partition of the posts table.
+
+Each class maps to a PostgreSQL partition table (e.g. posts_2024_01).
+These are real ORM models tracked by Alembic autogenerate.
+
+Uses ISO week numbering (datetime.isocalendar().week). ISO years can have
+52 or 53 weeks, and week boundaries are always Monday to Monday.
+"""
+
+from __future__ import annotations
+
+import sys
+from datetime import UTC, datetime
+
+from python.orm.data_science_dev.base import DataScienceDevBase
+from python.orm.data_science_dev.posts.columns import PostsColumns
+
+PARTITION_START_YEAR = 2023
+PARTITION_END_YEAR = 2026
+
+_current_module = sys.modules[__name__]
+
+
+def iso_weeks_in_year(year: int) -> int:
+    """Return the number of ISO weeks in a given year (52 or 53)."""
+    dec_28 = datetime(year, 12, 28, tzinfo=UTC)
+    return dec_28.isocalendar().week
+
+
+def week_bounds(year: int, week: int) -> tuple[datetime, datetime]:
+    """Return (start, end) datetimes for an ISO week.
+
+    Start = Monday 00:00:00 UTC of the given ISO week.
+    End   = Monday 00:00:00 UTC of the following ISO week.
+    """
+    start = datetime.fromisocalendar(year, week, 1).replace(tzinfo=UTC)
+    if week < iso_weeks_in_year(year):
+        end = datetime.fromisocalendar(year, week + 1, 1).replace(tzinfo=UTC)
+    else:
+        end = datetime.fromisocalendar(year + 1, 1, 1).replace(tzinfo=UTC)
+    return start, end
+
+
+def _build_partition_classes() -> dict[str, type]:
+    """Generate one ORM class per ISO week partition."""
+    classes: dict[str, type] = {}
+
+    for year in range(PARTITION_START_YEAR, PARTITION_END_YEAR + 1):
+        for week in range(1, iso_weeks_in_year(year) + 1):
+            class_name = f"PostsWeek{year}W{week:02d}"
+            table_name = f"posts_{year}_{week:02d}"
+
+            partition_class = type(
+                class_name,
+                (PostsColumns, DataScienceDevBase),
+                {
+                    "__tablename__": table_name,
+                    "__table_args__": ({"implicit_returning": False},),
+                },
+            )
+
+            classes[class_name] = partition_class
+
+    return classes
+
+
+# Generate all partition classes and register them on this module
+_partition_classes = _build_partition_classes()
+for _name, _cls in _partition_classes.items():
+    setattr(_current_module, _name, _cls)
+__all__ = list(_partition_classes.keys())

python/orm/data_science_dev/posts/tables.py

@@ -0,0 +1,13 @@
+"""Posts parent table with PostgreSQL weekly range partitioning on date column."""
+
+from __future__ import annotations
+
+from python.orm.data_science_dev.base import DataScienceDevBase
+from python.orm.data_science_dev.posts.columns import PostsColumns
+
+
+class Posts(PostsColumns, DataScienceDevBase):
+    """Parent partitioned table for posts, partitioned by week on `date`."""
+
+    __tablename__ = "posts"
+    __table_args__ = ({"postgresql_partition_by": "RANGE (date)"},)

python/orm/richie/__init__.py

@@ -3,7 +3,6 @@
 from __future__ import annotations
 
 from python.orm.richie.base import RichieBase, TableBase, TableBaseBig, TableBaseSmall
-from python.orm.richie.congress import Bill, Legislator, Vote, VoteRecord
 from python.orm.richie.contact import (
     Contact,
     ContactNeed,
@@ -13,17 +12,13 @@ from python.orm.richie.contact import (
 )
 
 __all__ = [
-    "Bill",
     "Contact",
     "ContactNeed",
     "ContactRelationship",
-    "Legislator",
     "Need",
     "RelationshipType",
     "RichieBase",
     "TableBase",
     "TableBaseBig",
     "TableBaseSmall",
-    "Vote",
-    "VoteRecord",
 ]

python/orm/richie/congress.py

@@ -1,150 +0,0 @@
-"""Congress Tracker database models."""
-
-from __future__ import annotations
-
-from datetime import date
-
-from sqlalchemy import ForeignKey, Index, Text, UniqueConstraint
-from sqlalchemy.orm import Mapped, mapped_column, relationship
-
-from python.orm.richie.base import RichieBase, TableBase
-
-
-class Legislator(TableBase):
-    """Legislator model - members of Congress."""
-
-    __tablename__ = "legislator"
-
-    # Natural key - bioguide ID is the authoritative identifier
-    bioguide_id: Mapped[str] = mapped_column(Text, unique=True, index=True)
-
-    # Other IDs for cross-referencing
-    thomas_id: Mapped[str | None]
-    lis_id: Mapped[str | None]
-    govtrack_id: Mapped[int | None]
-    opensecrets_id: Mapped[str | None]
-    fec_ids: Mapped[str | None]  # JSON array stored as string
-
-    # Name info
-    first_name: Mapped[str]
-    last_name: Mapped[str]
-    official_full_name: Mapped[str | None]
-    nickname: Mapped[str | None]
-
-    # Bio
-    birthday: Mapped[date | None]
-    gender: Mapped[str | None]  # M/F
-
-    # Current term info (denormalized for query efficiency)
-    current_party: Mapped[str | None]
-    current_state: Mapped[str | None]
-    current_district: Mapped[int | None]  # House only
-    current_chamber: Mapped[str | None]  # rep/sen
-
-    # Relationships
-    vote_records: Mapped[list[VoteRecord]] = relationship(
-        "VoteRecord",
-        back_populates="legislator",
-        cascade="all, delete-orphan",
-    )
-
-
-class Bill(TableBase):
-    """Bill model - legislation introduced in Congress."""
-
-    __tablename__ = "bill"
-
-    # Composite natural key: congress + bill_type + number
-    congress: Mapped[int]
-    bill_type: Mapped[str]  # hr, s, hres, sres, hjres, sjres
-    number: Mapped[int]
-
-    # Bill info
-    title: Mapped[str | None]
-    title_short: Mapped[str | None]
-    official_title: Mapped[str | None]
-
-    # Status
-    status: Mapped[str | None]
-    status_at: Mapped[date | None]
-
-    # Sponsor
-    sponsor_bioguide_id: Mapped[str | None]
-
-    # Subjects
-    subjects_top_term: Mapped[str | None]
-
-    # Relationships
-    votes: Mapped[list[Vote]] = relationship(
-        "Vote",
-        back_populates="bill",
-    )
-
-    __table_args__ = (
-        UniqueConstraint("congress", "bill_type", "number", name="uq_bill_congress_type_number"),
-        Index("ix_bill_congress", "congress"),
-    )
-
-
-class Vote(TableBase):
-    """Vote model - roll call votes in Congress."""
-
-    __tablename__ = "vote"
-
-    # Composite natural key: congress + chamber + session + number
-    congress: Mapped[int]
-    chamber: Mapped[str]  # house/senate
-    session: Mapped[int]
-    number: Mapped[int]
-
-    # Vote details
-    vote_type: Mapped[str | None]
-    question: Mapped[str | None]
-    result: Mapped[str | None]
-    result_text: Mapped[str | None]
-
-    # Timing
-    vote_date: Mapped[date]
-
-    # Vote counts (denormalized for efficiency)
-    yea_count: Mapped[int | None]
-    nay_count: Mapped[int | None]
-    not_voting_count: Mapped[int | None]
-    present_count: Mapped[int | None]
-
-    # Related bill (optional - not all votes are on bills)
-    bill_id: Mapped[int | None] = mapped_column(ForeignKey("main.bill.id"))
-
-    # Relationships
-    bill: Mapped[Bill | None] = relationship("Bill", back_populates="votes")
-    vote_records: Mapped[list[VoteRecord]] = relationship(
-        "VoteRecord",
-        back_populates="vote",
-        cascade="all, delete-orphan",
-    )
-
-    __table_args__ = (
-        UniqueConstraint("congress", "chamber", "session", "number", name="uq_vote_congress_chamber_session_number"),
-        Index("ix_vote_date", "vote_date"),
-        Index("ix_vote_congress_chamber", "congress", "chamber"),
-    )
-
-
-class VoteRecord(RichieBase):
-    """Association table: Vote <-> Legislator with position."""
-
-    __tablename__ = "vote_record"
-
-    vote_id: Mapped[int] = mapped_column(
-        ForeignKey("main.vote.id", ondelete="CASCADE"),
-        primary_key=True,
-    )
-    legislator_id: Mapped[int] = mapped_column(
-        ForeignKey("main.legislator.id", ondelete="CASCADE"),
-        primary_key=True,
-    )
-    position: Mapped[str]  # Yea, Nay, Not Voting, Present
-
-    # Relationships
-    vote: Mapped[Vote] = relationship("Vote", back_populates="vote_records")
-    legislator: Mapped[Legislator] = relationship("Legislator", back_populates="vote_records")

python/signal_bot/device_registry.py

@@ -63,9 +63,9 @@ class DeviceRegistry:
             return
 
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if device:
                 if device.safety_number != safety_number and device.trust_level != TrustLevel.BLOCKED:
@@ -99,9 +99,9 @@ class DeviceRegistry:
         Returns True if the device was found and verified.
         """
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if not device:
                 logger.warning(f"Cannot verify unknown device: {phone_number}")
@@ -139,9 +139,9 @@ class DeviceRegistry:
     def grant_role(self, phone_number: str, role: Role) -> bool:
         """Add a role to a device. Called by admin over SSH."""
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if not device:
                 logger.warning(f"Cannot grant role for unknown device: {phone_number}")
@@ -150,7 +150,7 @@ class DeviceRegistry:
             if any(record.name == role for record in device.roles):
                 return True
 
-            role_record = session.execute(select(RoleRecord).where(RoleRecord.name == role)).scalar_one_or_none()
+            role_record = session.scalars(select(RoleRecord).where(RoleRecord.name == role)).one_or_none()
 
             if not role_record:
                 logger.warning(f"Unknown role: {role}")
@@ -165,9 +165,9 @@ class DeviceRegistry:
     def revoke_role(self, phone_number: str, role: Role) -> bool:
         """Remove a role from a device. Called by admin over SSH."""
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if not device:
                 logger.warning(f"Cannot revoke role for unknown device: {phone_number}")
@@ -182,16 +182,16 @@ class DeviceRegistry:
     def set_roles(self, phone_number: str, roles: list[Role]) -> bool:
         """Replace all roles for a device. Called by admin over SSH."""
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if not device:
                 logger.warning(f"Cannot set roles for unknown device: {phone_number}")
                 return False
 
             role_names = [str(role) for role in roles]
-            records = list(session.execute(select(RoleRecord).where(RoleRecord.name.in_(role_names))).scalars().all())
+            records = session.scalars(select(RoleRecord).where(RoleRecord.name.in_(role_names))).all()
             device.roles = records
             session.commit()
             self._update_cache(phone_number, device)
@@ -203,7 +203,7 @@ class DeviceRegistry:
     def list_devices(self) -> list[SignalDevice]:
         """Return all known devices."""
         with Session(self.engine) as session:
-            return list(session.execute(select(SignalDevice)).scalars().all())
+            return list(session.scalars(select(SignalDevice)).all())
 
     def sync_identities(self) -> None:
         """Pull identity list from signal-cli and record any new ones."""
@@ -226,9 +226,7 @@ class DeviceRegistry:
     def _load_device(self, phone_number: str) -> SignalDevice | None:
         """Fetch a device by phone number (with joined roles)."""
         with Session(self.engine) as session:
-            return session.execute(
+            return session.scalars(select(SignalDevice).where(SignalDevice.phone_number == phone_number)).one_or_none()
-                select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
 
     def _update_cache(self, phone_number: str, device: SignalDevice) -> None:
         """Refresh the cache entry for a device."""
@@ -244,9 +242,9 @@ class DeviceRegistry:
     def _set_trust(self, phone_number: str, level: str, log_msg: str | None = None) -> bool:
         """Update the trust level for a device."""
         with Session(self.engine) as session:
-            device = session.execute(
+            device = session.scalars(
                 select(SignalDevice).where(SignalDevice.phone_number == phone_number)
-            ).scalar_one_or_none()
+            ).one_or_none()
 
             if not device:
                 return False
@@ -269,7 +267,7 @@ def sync_roles(engine: Engine) -> None:
     expected = {role.value for role in Role}
 
     with Session(engine) as session:
-        existing = {record.name for record in session.execute(select(RoleRecord)).scalars().all()}
+        existing = set(session.scalars(select(RoleRecord.name)).all())
 
         to_add = expected - existing
         to_remove = existing - expected

python/tools/snapshot_manager.py

@@ -34,8 +34,9 @@ def main(config_file: Path) -> None:
                 logger.error(msg)
                 signal_alert(msg)
                 continue
-
+            count_lookup = get_count_lookup(config_file, dataset.name)
-            get_snapshots_to_delete(dataset, get_count_lookup(config_file, dataset.name))
+            logger.info(f"using {count_lookup} for {dataset.name}")
+            get_snapshots_to_delete(dataset, count_lookup)
     except Exception:
         logger.exception("snapshot_manager failed")
         signal_alert("snapshot_manager failed")
@@ -99,6 +100,7 @@ def get_snapshots_to_delete(
     """
     snapshots = dataset.get_snapshots()
 
+    logger.info(f"calculating snapshots for {dataset.name} to be deleted")
     if not snapshots:
         logger.info(f"{dataset.name} has no snapshots")
         return

python/tools/whisper/Dockerfile

@@ -0,0 +1,17 @@
+FROM nvidia/cuda:12.4.1-cudnn-runtime-ubuntu22.04
+
+ENV DEBIAN_FRONTEND=noninteractive \
+    PYTHONDONTWRITEBYTECODE=1 \
+    PYTHONUNBUFFERED=1
+
+RUN apt-get update \
+    && apt-get install -y --no-install-recommends python3 python3-pip ffmpeg \
+    && rm -rf /var/lib/apt/lists/*
+
+RUN pip3 install --no-cache-dir --upgrade pip \
+    && pip3 install --no-cache-dir faster-whisper requests
+
+WORKDIR /app
+COPY python/tools/whisper/inference.py /app/inference.py
+
+ENTRYPOINT ["python3", "/app/inference.py"]

python/tools/whisper/Dockerfile.dockerignore

@@ -0,0 +1,2 @@
+*
+!python/tools/whisper/inference.py

python/tools/whisper/__init__.py

@@ -0,0 +1 @@
+"""Whisper transcription tools (host orchestrator and container entrypoint)."""

python/tools/whisper/inference.py

@@ -0,0 +1,136 @@
+"""Container entrypoint that transcribes a directory of audio files with faster-whisper.
+
+Run inside the whisper-transcribe docker image; segment timestamps are grouped
+into one-minute buckets so the output reads as ``[HH:MM:00] text``.
+"""
+
+from __future__ import annotations
+
+import argparse
+import logging
+from pathlib import Path
+
+from faster_whisper import WhisperModel
+
+logger = logging.getLogger(__name__)
+
+AUDIO_EXTENSIONS = {".mp3", ".wav", ".m4a", ".flac", ".ogg", ".opus", ".mp4", ".mkv", ".webm", ".aac"}
+BUCKET_SECONDS = 60
+BEAM_SIZE = 5
+SECONDS_PER_HOUR = 3600
+SECONDS_PER_MINUTE = 60
+
+
+def format_timestamp(total_seconds: float) -> str:
+    """Render a whole-minute timestamp as ``HH:MM:00``.
+
+    Args:
+        total_seconds: Offset in seconds from the start of the audio.
+
+    Returns:
+        A zero-padded ``HH:MM:00`` string.
+    """
+    hours = int(total_seconds // SECONDS_PER_HOUR)
+    minutes = int((total_seconds % SECONDS_PER_HOUR) // SECONDS_PER_MINUTE)
+    return f"{hours:02d}:{minutes:02d}:00"
+
+
+def transcribe_file(model: WhisperModel, audio_path: Path, output_path: Path) -> None:
+    """Transcribe one audio file and write the bucketed transcript to disk.
+
+    Args:
+        model: Loaded faster-whisper model.
+        audio_path: Source audio file.
+        output_path: Destination ``.txt`` path.
+    """
+    logger.info("Transcribing %s", audio_path)
+    segments, info = model.transcribe(
+        str(audio_path),
+        language="en",
+        beam_size=BEAM_SIZE,
+        vad_filter=True,
+    )
+    logger.info("Duration %.1fs", info.duration)
+
+    buckets: dict[int, list[str]] = {}
+    for segment in segments:
+        bucket = int(segment.start // BUCKET_SECONDS)
+        buckets.setdefault(bucket, []).append(segment.text.strip())
+
+    lines = [f"[{format_timestamp(bucket * BUCKET_SECONDS)}] {' '.join(buckets[bucket])}" for bucket in sorted(buckets)]
+    output_path.write_text("\n\n".join(lines) + "\n", encoding="utf-8")
+    logger.info("Wrote %s", output_path)
+
+
+def find_audio_files(input_directory: Path) -> list[Path]:
+    """Collect every audio file under ``input_directory``.
+
+    Args:
+        input_directory: Directory to walk recursively.
+
+    Returns:
+        Sorted list of audio file paths.
+    """
+    return sorted(
+        path for path in input_directory.rglob("*") if path.is_file() and path.suffix.lower() in AUDIO_EXTENSIONS
+    )
+
+
+def configure_container_logger() -> None:
+    """Configure logging for the container (stdout, INFO)."""
+    logging.basicConfig(
+        level=logging.INFO,
+        format="%(asctime)s %(levelname)s %(message)s",
+    )
+
+
+def parse_arguments() -> argparse.Namespace:
+    """Parse CLI arguments for the container entrypoint.
+
+    Returns:
+        Parsed argparse namespace.
+    """
+    parser = argparse.ArgumentParser(description=__doc__)
+    parser.add_argument("--input", type=Path, default=Path("/audio"))
+    parser.add_argument("--output", type=Path, default=Path("/output"))
+    parser.add_argument("--model", default="large-v3")
+    parser.add_argument(
+        "--download-only",
+        action="store_true",
+        help="Download the model into the cache volume and exit without transcribing.",
+    )
+    return parser.parse_args()
+
+
+def main() -> None:
+    """Load the model, then either exit (download-only) or transcribe the directory."""
+    configure_container_logger()
+    arguments = parse_arguments()
+
+    logger.info("Loading model %s on CUDA", arguments.model)
+    model = WhisperModel(arguments.model, device="cuda", compute_type="float16")
+
+    if arguments.download_only:
+        logger.info("Model ready; exiting (download-only mode)")
+        return
+
+    arguments.output.mkdir(parents=True, exist_ok=True)
+
+    audio_files = find_audio_files(arguments.input)
+    if not audio_files:
+        logger.warning("No audio files found in %s", arguments.input)
+        return
+
+    logger.info("Found %d audio file(s)", len(audio_files))
+    for audio_path in audio_files:
+        relative = audio_path.relative_to(arguments.input)
+        output_path = arguments.output / relative.with_suffix(".txt")
+        output_path.parent.mkdir(parents=True, exist_ok=True)
+        if output_path.exists():
+            logger.info("Skip %s (already transcribed)", relative)
+            continue
+        transcribe_file(model, audio_path, output_path)
+
+
+if __name__ == "__main__":
+    main()

python/tools/whisper/transcribe.py

@@ -0,0 +1,167 @@
+"""Build and run the whisper transcription docker container on demand.
+
+The container is started fresh for each invocation and removed on exit
+(``docker run --rm``). The model is cached in a named docker volume so
+only the first run pays the download cost.
+"""
+
+from __future__ import annotations
+
+import logging
+import subprocess
+from pathlib import Path
+from typing import Annotated
+
+import typer
+
+from python.common import configure_logger
+
+logger = logging.getLogger(__name__)
+
+
+class Config:
+    """Paths and names for the whisper-transcribe Docker workflow."""
+
+    image_tag = "whisper-transcribe:latest"
+    model_volume = "whisper-models"
+    repo_root = Path(__file__).resolve().parents[3]
+    dockerfile = Path(__file__).resolve().parent / "Dockerfile"
+    huggingface_cache = "/root/.cache/huggingface"
+
+
+def run_docker(arguments: list[str]) -> None:
+    """Run a docker subcommand, streaming output and raising on failure.
+
+    Args:
+        arguments: Arguments to pass to the ``docker`` binary.
+
+    Raises:
+        subprocess.CalledProcessError: If docker exits non-zero.
+    """
+    logger.info("docker %s", " ".join(arguments))
+    subprocess.run(["docker", *arguments], check=True)
+
+
+def build_image() -> None:
+    """Build the whisper-transcribe image using the repo root as build context."""
+    logger.info("Building image %s", Config.image_tag)
+    run_docker(
+        [
+            "build",
+            "--tag",
+            Config.image_tag,
+            "--file",
+            str(Config.dockerfile),
+            str(Config.repo_root),
+        ],
+    )
+
+
+def model_cache_present(model: str) -> bool:
+    """Check whether the given model is already downloaded in the cache volume.
+
+    Args:
+        model: faster-whisper model name (e.g. ``large-v3``).
+
+    Returns:
+        True if the HuggingFace cache directory for the model exists in the volume.
+    """
+    cache_directory = f"hub/models--Systran--faster-whisper-{model}"
+    completed = subprocess.run(
+        [
+            "docker",
+            "run",
+            "--rm",
+            "--volume",
+            f"{Config.model_volume}:/cache",
+            "alpine",
+            "test",
+            "-d",
+            f"/cache/{cache_directory}",
+        ],
+        check=False,
+    )
+    return completed.returncode == 0
+
+
+def download_model(model: str) -> None:
+    """Download the model into the cache volume and exit.
+
+    Args:
+        model: faster-whisper model name.
+    """
+    logger.info("Downloading model %s into volume %s", model, Config.model_volume)
+    run_docker(
+        [
+            "run",
+            "--rm",
+            "--device=nvidia.com/gpu=all",
+            "--ipc=host",
+            "--volume",
+            f"{Config.model_volume}:{Config.huggingface_cache}",
+            Config.image_tag,
+            "--model",
+            model,
+            "--download-only",
+        ],
+    )
+
+
+def transcribe(input_directory: Path, output_directory: Path, model: str) -> None:
+    """Run transcription on every audio file under ``input_directory``.
+
+    Args:
+        input_directory: Host path containing audio files (mounted read-only).
+        output_directory: Host path for ``.txt`` transcripts.
+        model: faster-whisper model name.
+    """
+    logger.info("Transcribing %s -> %s (model=%s)", input_directory, output_directory, model)
+    run_docker(
+        [
+            "run",
+            "--rm",
+            "--device=nvidia.com/gpu=all",
+            "--ipc=host",
+            "--volume",
+            f"{input_directory}:/audio:ro",
+            "--volume",
+            f"{output_directory}:/output",
+            "--volume",
+            f"{Config.model_volume}:{Config.huggingface_cache}",
+            Config.image_tag,
+            "--model",
+            model,
+        ],
+    )
+
+
+def main(
+    input_directory: Annotated[Path, typer.Argument(help="Directory of audio files to transcribe.")],
+    output_directory: Annotated[Path, typer.Argument(help="Directory to write .txt transcripts to.")],
+    model: Annotated[str, typer.Option(help="faster-whisper model name.")] = "large-v3",
+    *,
+    force_download: Annotated[
+        bool,
+        typer.Option("--force-download", help="Re-download the model even if already cached."),
+    ] = False,
+) -> None:
+    """Build the image, ensure the model is cached, then transcribe and stop."""
+    configure_logger()
+
+    resolved_input = input_directory.resolve(strict=True)
+    output_directory.mkdir(parents=True, exist_ok=True)
+    resolved_output = output_directory.resolve()
+
+    build_image()
+
+    if force_download or not model_cache_present(model):
+        download_model(model)
+    else:
+        logger.info("Model %s already cached in volume %s", model, Config.model_volume)
+
+    transcribe(resolved_input, resolved_output, model)
+    logger.info("Done. Container stopped.")
+
+
+if __name__ == "__main__":
+    typer.run(main)

systems/bob/default.nix

@@ -1,9 +1,10 @@
-{ inputs, ... }:
+{ inputs, pkgs, ... }:
 {
   imports = [
+    "${inputs.self}/users/math"
     "${inputs.self}/users/richie"
+    "${inputs.self}/users/steve"
     "${inputs.self}/common/global"
-    "${inputs.self}/common/optional/desktop.nix"
     "${inputs.self}/common/optional/docker.nix"
     "${inputs.self}/common/optional/scanner.nix"
     "${inputs.self}/common/optional/steam.nix"
@@ -12,13 +13,17 @@
     "${inputs.self}/common/optional/update.nix"
     "${inputs.self}/common/optional/yubikey.nix"
     "${inputs.self}/common/optional/zerotier.nix"
-    "${inputs.self}/common/optional/brain_substituter.nix"
     "${inputs.self}/common/optional/nvidia.nix"
     ./hardware.nix
     ./syncthing.nix
     ./llms.nix
   ];
 
+  boot = {
+    kernelPackages = pkgs.linuxPackages_6_18;
+    zfs.package = pkgs.zfs_2_4;
+  };
+
   networking = {
     hostName = "bob";
     hostId = "7c678a41";

systems/bob/hardware.nix

@@ -28,7 +28,6 @@
         allowDiscards = true;
         keyFileSize = 4096;
         keyFile = "/dev/disk/by-id/usb-Samsung_Flash_Drive_FIT_0374620080067131-0:0";
-        fallbackToPassword = true;
       };
     };
     kernelModules = [ "kvm-amd" ];

systems/bob/llms.nix

@@ -23,6 +23,7 @@
       "magistral:24b"
       "ministral-3:14b"
       "nemotron-3-nano:30b"
+      "nemotron-3-nano:4b"
       "nemotron-cascade-2:30b"
       "qwen3-coder:30b"
       "qwen3-embedding:0.6b"
@@ -44,6 +45,7 @@
       "translategemma:12b"
       "translategemma:27b"
       "translategemma:4b"
+      "rinex20/translategemma3:12b"
     ];
     models = "/zfs/models";
     openFirewall = true;

systems/bob/syncthing.nix

@@ -31,5 +31,15 @@
       ];
       fsWatcherEnabled = true;
     };
+    "recordings" = {
+      path = "/home/richie/recordings";
+      devices = [
+        "jeeves"
+        "phone"
+        "rhapsody-in-green"
+      ];
+      fsWatcherEnabled = true;
+    };
+
   };
 }

systems/brain/hardware.nix

@@ -26,7 +26,6 @@
         allowDiscards = true;
         keyFileSize = 4096;
         keyFile = "/dev/disk/by-id/usb-USB_SanDisk_3.2Gen1_03021630090925173333-0:0";
-        fallbackToPassword = true;
       };
     };
     kernelModules = [ "kvm-intel" ];

systems/jeeves/default.nix

@@ -4,9 +4,10 @@ let
 in
 {
   imports = [
-    "${inputs.self}/users/richie"
-    "${inputs.self}/users/math"
     "${inputs.self}/users/dov"
+    "${inputs.self}/users/math"
+    "${inputs.self}/users/richie"
+    "${inputs.self}/users/steve"
     "${inputs.self}/common/global"
     "${inputs.self}/common/optional/docker.nix"
     "${inputs.self}/common/optional/ssh_decrypt.nix"
@@ -15,6 +16,7 @@ in
     "${inputs.self}/common/optional/zerotier.nix"
     ./docker
     ./services
+    ./web_services
     ./hardware.nix
     ./networking.nix
     ./programs.nix

systems/jeeves/hardware.nix

@@ -9,7 +9,6 @@ let
     inherit device;
     keyFileSize = 4096;
     keyFile = "/dev/disk/by-id/usb-XIAO_USB_Drive_24587CE29074-0:0";
-    fallbackToPassword = true;
   };
   makeLuksSSD =
     device:

systems/jeeves/scripts/zfs.sh

@@ -15,18 +15,20 @@ sudo zpool add storage -o ashift=12 logs mirror
 sudo zpool create scratch -o ashift=12 -O acltype=posixacl -O atime=off -O dnodesize=auto -O xattr=sa -O compression=zstd -O encryption=aes-256-gcm -O keyformat=hex -O keylocation=file:///key -m /zfs/scratch
 
 # media datasets
+sudo zfs create media/temp -o sync=disabled -o redundant_metadata=none
 sudo zfs create media/secure -o encryption=aes-256-gcm -o keyformat=hex -o keylocation=file:///root/zfs.key
 sudo zfs create media/secure/docker -o compression=zstd-9
 sudo zfs create media/secure/github-runners -o compression=zstd-9 -o sync=disabled
 sudo zfs create media/secure/home_assistant -o compression=zstd-19
 sudo zfs create media/secure/notes -o copies=2
-sudo zfs create media/secure/postgres -o recordsize=16k -o primarycache=metadata
+sudo zfs create media/secure/postgres -o mountpoint=/zfs/media/database/postgres -o recordsize=16k -o primarycache=metadata
+sudo zfs create media/secure/postgres-wal -o mountpoint=/zfs/media/database/postgres-wal -o recordsize=32k -o primarycache=metadata -o special_small_blocks=32K -o compression=lz4 -o secondarycache=none -o logbias=latency
 sudo zfs create media/secure/services -o compression=zstd-9
 sudo zfs create media/secure/share -o mountpoint=/zfs/media/share -o exec=off
 
 # scratch datasets
 sudo zfs create scratch/kafka -o mountpoint=/zfs/scratch/kafka -o recordsize=1M
-sudo zfs create scratch/transmission -o mountpoint=/zfs/scratch/transmission -o recordsize=16k -o sync=disabled
+sudo zfs create scratch/transmission -o mountpoint=/zfs/scratch/transmission -o recordsize=16k -o sync=disabled -o redundant_metadata=none
 
 # storage datasets
 sudo zfs create storage/ollama -o recordsize=1M -o compression=zstd-19 -o sync=disabled

systems/jeeves/services/cloud_flare_tunnel.nix

@@ -1,17 +0,0 @@
-{ pkgs, ... }:
-let
-  vars = import ../vars.nix;
-in
-{
-  systemd.services.cloud_flare_tunnel = {
-    description = "cloud_flare_tunnel proxy's traffic through cloudflare";
-    after = [ "network.target" ];
-    wantedBy = [ "multi-user.target" ];
-    serviceConfig = {
-      Type = "simple";
-      EnvironmentFile = "${vars.secrets}/docker/cloud_flare_tunnel";
-      ExecStart = "${pkgs.cloudflared}/bin/cloudflared --no-autoupdate tunnel run";
-      Restart = "on-failure";
-    };
-  };
-}

systems/jeeves/services/gitea.nix

@@ -2,7 +2,10 @@ let
   vars = import ../vars.nix;
 in
 {
-  networking.firewall.allowedTCPPorts = [ 6443 ];
+  networking.firewall.allowedTCPPorts = [
+    6443
+    2223
+  ];
 
   services.gitea = {
     enable = true;
@@ -24,7 +27,7 @@ in
         ROOT_URL = "https://gitea.tmmworkshop.com/";
         HTTP_PORT = 6443;
         SSH_PORT = 2223;
-        SSH_LISTEN_PORT = 2224;
+        SSH_LISTEN_PORT = 2223;
         START_SSH_SERVER = true;
         PUBLIC_URL_DETECTION = "auto";
       };

systems/jeeves/services/hedgedoc.nix

@@ -0,0 +1,24 @@
+{
+  services.hedgedoc = {
+    enable = true;
+    settings = {
+      host = "0.0.0.0";
+      port = 3000;
+      domain = "192.168.90.40";
+      urlAddPort = true;
+      protocolUseSSL = false;
+      db = {
+        dialect = "postgres";
+        database = "hedgedoc";
+        username = "hedgedoc";
+        host = "/run/postgresql";
+      };
+    };
+  };
+  networking.firewall.allowedTCPPorts = [ 3000 ];
+
+  systemd.services.hedgedoc = {
+    after = [ "postgresql.service" ];
+    requires = [ "postgresql.service" ];
+  };
+}

systems/jeeves/services/kafka.nix

@@ -3,7 +3,7 @@ let
 in
 {
   services.apache-kafka = {
-    enable = true;
+    enable = false;
     settings = {
       listeners = [ "PLAINTEXT://localhost:9092" ];
       "log.dirs" = [ vars.kafka ];

systems/jeeves/services/postgress.nix

@@ -5,6 +5,10 @@ in
 {
   networking.firewall.allowedTCPPorts = [ 5432 ];
 
+  # Symlink pg_wal to a ZFS dataset on the special (metadata) vdev for fast WAL writes
+  # this is required for systemd sandboxing
+  systemd.services.postgresql.serviceConfig.ReadWritePaths = [ "/zfs/media/database/postgres-wal" ];
+
   services.postgresql = {
     enable = true;
     package = pkgs.postgresql_17_jit;
@@ -33,12 +37,19 @@ in
       # signalbot
       local signalbot  signalbot   trust
 
+      # hedgedoc
+      local hedgedoc   hedgedoc    trust
+
       # math
       local postgres  math   trust
       host  postgres  math   127.0.0.1/32    trust
       host  postgres  math   ::1/128         trust
       host  postgres  math   192.168.90.1/24 trust
 
+      local data_science_dev  math   trust
+      host  data_science_dev  math   127.0.0.1/32    trust
+      host  data_science_dev  math   ::1/128         trust
+      host  data_science_dev  math   192.168.90.1/24 trust
     '';
 
     identMap = ''
@@ -108,10 +119,19 @@ in
           login = true;
         };
       }
+      {
+        name = "hedgedoc";
+        ensureDBOwnership = true;
+        ensureClauses = {
+          login = true;
+        };
+      }
     ];
     ensureDatabases = [
+      "data_science_dev"
       "hass"
       "gitea"
+      "hedgedoc"
       "math"
       "n8n"
       "richie"

systems/jeeves/services/validate_system.toml

@@ -1,7 +1,6 @@
 zpool = ["root_pool", "storage", "media"]
 services = [
     "audiobookshelf",
-    "cloud_flare_tunnel",
     "haproxy",
     "docker",
     "home-assistant",

systems/jeeves/snapshot_config.toml

@@ -4,6 +4,7 @@ hourly = 24
 daily = 0
 monthly = 0
 
+# root_pool
 ["root_pool/home"]
 15_min = 8
 hourly = 24
@@ -27,57 +28,96 @@ monthly = 0
 hourly = 24
 daily = 30
 monthly = 6
+# storage
+["storage/ollama"]
+15_min = 2
+hourly = 0
+daily = 0
+monthly = 0
 
-["storage/plex"]
+["storage/secure"]
+15_min = 0
+hourly = 0
+daily = 0
+monthly = 0
+
+["storage/secure/plex"]
 15_min = 6
 hourly = 2
 daily = 1
 monthly = 0
 
-["media/plex"]
+["storage/secure/transmission"]
-15_min = 6
+15_min = 4
-hourly = 2
+hourly = 0
-daily = 1
+daily = 0
 monthly = 0
 
-["media/notes"]
+["storage/secure/secrets"]
 15_min = 8
 hourly = 24
 daily = 30
 monthly = 12
 
-["media/docker"]
+# media
-15_min = 3
+["media/temp"]
-hourly = 12
+15_min = 2
-daily = 14
+hourly = 0
-monthly = 2
+daily = 0
-
+monthly = 0
-["media/services"]
+
-15_min = 3
+["media/secure"]
-hourly = 12
+15_min = 0
-daily = 14
+hourly = 0
-monthly = 2
+daily = 0
-
+monthly = 0
-["media/home_assistant"]
+
+["media/secure/plex"]
+15_min = 6
+hourly = 2
+daily = 1
+monthly = 0
+
+["media/secure/postgres-wal"]
+15_min = 4
+hourly = 2
+daily = 0
+monthly = 0
+
+
+["media/secure/postgres"]
+15_min = 8
+hourly = 24
+daily = 7
+monthly = 0
+
+["media/secure/share"]
+15_min = 4
+hourly = 0
+daily = 0
+monthly = 0
+
+["media/secure/github-runners"]
+15_min = 6
+hourly = 2
+daily = 1
+monthly = 0
+
+["media/secure/notes"]
+15_min = 8
+hourly = 24
+daily = 30
+monthly = 12
+
+["media/secure/docker"]
 15_min = 3
 hourly = 12
 daily = 14
 monthly = 2
 
+# scratch
 ["scratch/transmission"]
-15_min = 0
+15_min = 2
-hourly = 0
-daily = 0
-monthly = 0
-
-["storage/transmission"]
-15_min = 0
-hourly = 0
-daily = 0
-monthly = 0
-
-["storage/ollama"]
-15_min = 0
 hourly = 0
 daily = 0
 monthly = 0

systems/jeeves/syncthing.nix

@@ -89,7 +89,16 @@ in
           ];
           fsWatcherEnabled = true;
         };
-        #
+        "recordings" = {
+          path = "/home/richie/recordings";
+          devices = [
+            "bob"
+            "phone"
+            "rhapsody-in-green"
+          ];
+          fsWatcherEnabled = true;
+        };
+        # davids-server
         "davids-backup1" = {
           id = "8229p-8z3tm"; # cspell:disable-line
           path = "${vars.syncthing}/davids_backups/1";

systems/jeeves/web_services/acme.nix

@@ -0,0 +1,62 @@
+let
+  domains = [
+    "audiobookshelf"
+    "cache"
+    "gitea"
+    "jellyfin"
+    "share"
+  ];
+
+  makeCert = name: {
+    name = "${name}.tmmworkshop.com";
+    value = {
+      webroot = "/var/lib/acme/.challenges";
+      group = "acme";
+      reloadServices = [ "haproxy.service" ];
+    };
+  };
+
+  acmeServices = map (domain: "acme-${domain}.tmmworkshop.com.service") domains;
+in
+{
+  users.users.haproxy.extraGroups = [ "acme" ];
+
+  security.acme = {
+    acceptTerms = true;
+    defaults.email = "Richie@tmmworkshop.com";
+    certs = builtins.listToAttrs (map makeCert domains);
+  };
+
+  # Minimal nginx to serve ACME HTTP-01 challenge files for HAProxy
+  services.nginx = {
+    enable = true;
+    virtualHosts."acme-challenge" = {
+      listen = [
+        {
+          addr = "127.0.0.1";
+          port = 8402;
+        }
+      ];
+      locations."/.well-known/acme-challenge/" = {
+        root = "/var/lib/acme/.challenges";
+      };
+    };
+  };
+
+  # Ensure the challenge directory exists with correct permissions
+  systemd.tmpfiles.rules = [
+    "d /var/lib/acme/.challenges 0750 acme acme - -"
+    "d /var/lib/acme/.challenges/.well-known 0750 acme acme - -"
+    "d /var/lib/acme/.challenges/.well-known/acme-challenge 0750 acme acme - -"
+  ];
+
+  users.users.nginx.extraGroups = [ "acme" ];
+
+  # HAProxy needs certs to exist before it can bind :443.
+  # NixOS's acme module generates self-signed placeholders on first boot
+  # via acme-<domain>.service — just make HAProxy wait for them.
+  systemd.services.haproxy = {
+    after = acmeServices;
+    wants = acmeServices;
+  };
+}

systems/jeeves/web_services/default.nix

@@ -0,0 +1,9 @@
+{ lib, ... }:
+{
+  imports =
+    let
+      files = builtins.attrNames (builtins.readDir ./.);
+      nixFiles = builtins.filter (name: lib.hasSuffix ".nix" name && name != "default.nix") files;
+    in
+    map (file: ./. + "/${file}") nixFiles;
+}

systems/jeeves/web_services/haproxy.cfg

@@ -6,6 +6,7 @@ global
 defaults
   log global
   mode http
+  option httplog
   retries 3
   maxconn 2000
   timeout connect 5s
@@ -22,25 +23,37 @@ defaults
 #Application Setup
 frontend ContentSwitching
   bind *:80 v4v6
-  bind *:443 v4v6 ssl crt /zfs/storage/secrets/docker/cloudflare.pem
+  bind *:443 v4v6 ssl crt /var/lib/acme/audiobookshelf.tmmworkshop.com/full.pem crt /var/lib/acme/cache.tmmworkshop.com/full.pem crt /var/lib/acme/jellyfin.tmmworkshop.com/full.pem crt /var/lib/acme/share.tmmworkshop.com/full.pem crt /var/lib/acme/gitea.tmmworkshop.com/full.pem
   mode  http
+
+  # ACME challenge routing (must be first)
+  acl is_acme path_beg /.well-known/acme-challenge/
+  use_backend acme_challenge if is_acme
+
   # tmmworkshop.com
   acl host_audiobookshelf  hdr(host) -i audiobookshelf.tmmworkshop.com
   acl host_cache  hdr(host) -i cache.tmmworkshop.com
   acl host_jellyfin  hdr(host) -i jellyfin.tmmworkshop.com
   acl host_share  hdr(host) -i share.tmmworkshop.com
-  acl host_gcw  hdr(host) -i gcw.tmmworkshop.com
-  acl host_n8n  hdr(host) -i n8n.tmmworkshop.com
   acl host_gitea  hdr(host) -i gitea.tmmworkshop.com
 
+  # Hosts allowed to serve plain HTTP (add entries to skip the HTTPS redirect)
+  acl allow_http hdr(host) -i __none__
+  # acl allow_http hdr(host) -i example.tmmworkshop.com
+
+  # Redirect all HTTP to HTTPS unless on the allow list or ACME challenge
+  http-request redirect scheme https code 301 if !{ ssl_fc } !allow_http !is_acme
+
   use_backend audiobookshelf_nodes if host_audiobookshelf
   use_backend cache_nodes  if host_cache
   use_backend jellyfin if host_jellyfin
   use_backend share_nodes  if host_share
-  use_backend gcw_nodes  if host_gcw
-  use_backend n8n  if host_n8n
   use_backend gitea  if host_gitea
 
+backend acme_challenge
+  mode http
+  server acme 127.0.0.1:8402
+
 backend audiobookshelf_nodes
   mode http
   server server 127.0.0.1:8000
@@ -60,14 +73,6 @@ backend share_nodes
   mode http
   server server 127.0.0.1:8091
 
-backend gcw_nodes
-  mode http
-  server server 127.0.0.1:8092
-
-backend n8n
-  mode http
-  server server 127.0.0.1:5678
-
 backend gitea
   mode http
-  server server 127.0.0.1:6443
+  server server 127.0.0.1:6443

systems/rhapsody-in-green/default.nix

@@ -14,6 +14,7 @@
     ./llms.nix
     ./open_webui.nix
     ./qmk.nix
+    ./sunshine.nix
     ./syncthing.nix
     inputs.nixos-hardware.nixosModules.framework-13-7040-amd
   ];
@@ -23,11 +24,19 @@
     hostId = "6404140d";
     firewall = {
       enable = true;
-      allowedTCPPorts = [ ];
+      allowedTCPPorts = [
+        8000
+        8080
+      ];
     };
     networkmanager.enable = true;
   };
 
+  programs.appimage = {
+    enable = true;
+    binfmt = true; # allows *.AppImage to be run directly
+  };
+
   services = {
     openssh.ports = [ 922 ];
     flatpak.enable = true;

systems/rhapsody-in-green/llms.nix

@@ -8,9 +8,8 @@
       "deepscaler:1.5b"
       "deepseek-r1:8b"
       "gemma3:12b"
-      "gemma3:27b"
-      "gpt-oss:20b"
       "lfm2:24b"
+      "nemotron-3-nano:4b"
       "qwen3:14b"
       "qwen3.5:27b"
     ];

systems/rhapsody-in-green/sunshine.nix

@@ -0,0 +1,28 @@
+{ pkgs, ... }:
+{
+  services.sunshine = {
+    enable = true;
+    openFirewall = true;
+    capSysAdmin = true;
+  };
+  environment.systemPackages = [ pkgs.kdePackages.libkscreen ];
+
+  boot = {
+    kernelParams = [
+      "drm.edid_firmware=DP-4:edid/virtual-display.bin"
+      "video=DP-4:e"
+    ];
+  };
+
+  hardware.firmware = [
+    (pkgs.runCommandLocal "virtual-display-edid"
+      {
+        compressFirmware = false;
+      }
+      ''
+        mkdir -p $out/lib/firmware/edid
+        cp ${./edid/virtual-display.bin} $out/lib/firmware/edid/virtual-display.bin
+      ''
+    )
+  ];
+}

systems/rhapsody-in-green/syncthing.nix

@@ -55,6 +55,15 @@
       ];
       fsWatcherEnabled = true;
     };
+    "recordings" = {
+      path = "/home/richie/recordings";
+      devices = [
+        "bob"
+        "jeeves"
+        "phone"
+      ];
+      fsWatcherEnabled = true;
+    };
     "vault" = {
       path = "/home/richie/vault";
       devices = [

tests/test_signal_bot.py

@@ -210,9 +210,9 @@ class TestContactCache:
             mock_session_cls.return_value.__exit__ = MagicMock(return_value=False)
             mock_device = MagicMock()
             mock_device.trust_level = TrustLevel.UNVERIFIED
-            mock_session.execute.return_value.scalar_one_or_none.return_value = mock_device
+            mock_session.scalars.return_value.one_or_none.return_value = mock_device
             registry.record_contact("+1234", "abc")
-            mock_session.execute.assert_called_once()
+            mock_session.scalars.assert_called_once()
 
 
 class TestLocationCommand:

tools/line_counter.py

@@ -1,86 +0,0 @@
-"""Count lines of code in the repository, grouped by file type."""
-
-from __future__ import annotations
-
-import subprocess
-from collections import defaultdict
-from pathlib import Path
-
-
-def get_tracked_files() -> list[str]:
-    """Get all git-tracked files."""
-    result = subprocess.run(
-        ["git", "ls-files"],  # noqa: S603, S607
-        capture_output=True,
-        text=True,
-        check=True,
-    )
-    return [f for f in result.stdout.strip().splitlines() if f]
-
-
-def count_lines(filepath: str) -> int:
-    """Count lines in a file, returning 0 for binary files."""
-    try:
-        return len(Path(filepath).read_text(encoding="utf-8").splitlines())
-    except (UnicodeDecodeError, OSError):
-        return 0
-
-
-def count_lines_by_type() -> dict[str, int]:
-    """Count lines grouped by file extension."""
-    lines_by_type: dict[str, int] = defaultdict(int)
-    for filepath in get_tracked_files():
-        ext = Path(filepath).suffix.lstrip(".")
-        if not ext:
-            ext = Path(filepath).name
-        lines_by_type[ext] += count_lines(filepath)
-    # Exclude binary/non-code files
-    for key in ("png", "lock"):
-        lines_by_type.pop(key, None)
-    return dict(sorted(lines_by_type.items(), key=lambda x: x[1], reverse=True))
-
-
-def format_report() -> str:
-    """Generate a formatted line count report."""
-    lines_by_type = count_lines_by_type()
-    total = sum(lines_by_type.values())
-
-    lines = [
-        f"This repo has **{total:,}** lines of technical debt.",
-        "",
-        "| File Type | Lines | Percentage |",
-        "|-----------|------:|-----------:|",
-    ]
-    for ext, count in lines_by_type.items():
-        if count > 0:
-            pct = count / total * 100
-            prefix = "." if not ext.startswith(".") else ""
-            lines.append(f"| {prefix}{ext} | {count:,} | {pct:.1f}% |")
-
-    return "\n".join(lines)
-
-
-def update_readme() -> None:
-    """Update README.md with the line count report."""
-    readme_path = Path("README.md")
-    report = format_report()
-
-    start_marker = "<!-- LINE-COUNT-START -->"
-    end_marker = "<!-- LINE-COUNT-END -->"
-
-    content = readme_path.read_text(encoding="utf-8")
-
-    section = f"{start_marker}\n{report}\n{end_marker}"
-
-    if start_marker in content:
-        start = content.index(start_marker)
-        end = content.index(end_marker) + len(end_marker)
-        content = content[:start] + section + content[end:]
-    else:
-        content = content.rstrip() + "\n\n" + section + "\n"
-
-    readme_path.write_text(content, encoding="utf-8")
-
-
-if __name__ == "__main__":
-    update_readme()

users/dov/home/cli/git.nix

@@ -1,6 +1,7 @@
 {
   programs.git = {
     enable = true;
+    signing.format = null;
     settings = {
       user = {
         email = "dov.kruger@gmail.com";

users/elise/home/cli/git.nix

@@ -1,6 +1,7 @@
 {
   programs.git = {
     enable = true;
+    signing.format = null;
     settings = {
       user = {
         email = "DumbPuppy208@gmail.com";

users/math/home/cli/git.nix

@@ -1,6 +1,7 @@
 {
   programs.git = {
     enable = true;
+    signing.format = null;
     settings = {
       user = {
         email = "matthew.michal11@gmail.com";

users/math/systems/bob.nix

@@ -0,0 +1,5 @@
+{
+  imports = [
+    ../home/global.nix
+  ];
+}

users/richie/home/cli/git.nix

@@ -1,6 +1,7 @@
 {
   programs.git = {
     enable = true;
+    signing.format = null;
     settings = {
       user = {
         email = "Richie@tmmworkshop.com";

users/richie/home/gui/default.nix

@@ -22,9 +22,10 @@
     chromium
     # dev tools
     claude-code
+    codex
     gparted
     jetbrains.datagrip
-    proxychains
     opencode
+    proxychains
   ];
 }

users/richie/home/gui/vscode/settings.json

@@ -20,15 +20,15 @@
   // turns off all sounds and announcements
   "accessibility.signals.terminalCommandFailed": {
     "sound": "off",
-    "announcement": "off"
+    "announcement": "off",
   },
   "accessibility.signals.terminalQuickFix": {
     "sound": "off",
-    "announcement": "off"
+    "announcement": "off",
   },
   "accessibility.signals.terminalBell": {
     "sound": "off",
-    "announcement": "off"
+    "announcement": "off",
   },
 
   // database settings
@@ -41,8 +41,8 @@
       "driver": "PostgreSQL",
       "name": "main",
       "database": "postgres",
-      "username": "richie"
+      "username": "richie",
-    }
+    },
   ],
 
   // formatters
@@ -55,7 +55,7 @@
   "[yaml]": { "editor.defaultFormatter": "redhat.vscode-yaml" },
   "[javascriptreact]": { "editor.defaultFormatter": "esbenp.prettier-vscode" },
   "[github-actions-workflow]": {
-    "editor.defaultFormatter": "redhat.vscode-yaml"
+    "editor.defaultFormatter": "redhat.vscode-yaml",
   },
   "[dockercompose]": {
     "editor.insertSpaces": true,
@@ -64,9 +64,9 @@
     "editor.quickSuggestions": {
       "other": true,
       "comments": false,
-      "strings": true
+      "strings": true,
     },
-    "editor.defaultFormatter": "redhat.vscode-yaml"
+    "editor.defaultFormatter": "redhat.vscode-yaml",
   },
 
   // spell check
@@ -78,7 +78,8 @@
     "Corvidae",
     "drivername",
     "fastapi",
-    "syncthing"
+    "sandboxing",
+    "syncthing",
   ],
 
   // nix
@@ -96,5 +97,5 @@
   // new
   "hediet.vscode-drawio.resizeImages": null,
   "hediet.vscode-drawio.appearance": "automatic",
-  "claudeCode.preferredLocation": "panel"
+  "claudeCode.preferredLocation": "panel",
 }

users/richie/systems/bob.nix

@@ -1,6 +1,5 @@
 {
   imports = [
     ../home/global.nix
-    ../home/gui
   ];
 }

users/steve/default.nix

@@ -0,0 +1,44 @@
+{
+  pkgs,
+  config,
+  ...
+}:
+let
+  ifTheyExist = groups: builtins.filter (group: builtins.hasAttr group config.users.groups) groups;
+in
+{
+
+  users = {
+    users.steve = {
+      isNormalUser = true;
+
+      shell = pkgs.zsh;
+      group = "steve";
+      openssh.authorizedKeys.keys = [
+        "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIJH03VzDbUhzfhvwD+OsYh6GobODYaI9jdNdzWQoqFsp matth@Jove" # cspell:disable-line
+      ];
+      extraGroups = [
+        "audio"
+        "video"
+        "wheel"
+        "users"
+      ]
+      ++ ifTheyExist [
+        "dialout"
+        "docker"
+        "hass"
+        "libvirtd"
+        "networkmanager"
+        "plugdev"
+        "scanner"
+        "transmission"
+        "uaccess"
+        "wireshark"
+      ];
+      uid = 1005;
+    };
+
+    groups.steve.gid = 1005;
+  };
+  home-manager.users.steve = import ./systems/${config.networking.hostName}.nix;
+}

users/steve/home/cli/default.nix

@@ -0,0 +1,9 @@
+{
+  imports = [
+    ./direnv.nix
+    ./git.nix
+    ./zsh.nix
+  ];
+
+  programs.starship.enable = true;
+}

users/steve/home/cli/direnv.nix

@@ -0,0 +1,8 @@
+{
+
+  programs.direnv = {
+    enable = true;
+    enableZshIntegration = true;
+    nix-direnv.enable = true;
+  };
+}

users/steve/home/cli/git.nix

@@ -0,0 +1,15 @@
+{
+  programs.git = {
+    enable = true;
+    signing.format = null;
+    settings = {
+      user = {
+        email = "matthew.michal11@gmail.com";
+        name = "Matthew Michal";
+      };
+      pull.rebase = true;
+      color.ui = true;
+    };
+    lfs.enable = true;
+  };
+}

users/steve/home/cli/zsh.nix

@@ -0,0 +1,28 @@
+{
+  programs.zsh = {
+    enable = true;
+    syntaxHighlighting.enable = true;
+    history.size = 10000;
+    oh-my-zsh = {
+      enable = true;
+      plugins = [
+        "git"
+        "docker"
+        "docker-compose"
+        "colored-man-pages"
+        "rust"
+        "systemd"
+        "tmux"
+        "ufw"
+        "z"
+      ];
+    };
+    shellAliases = {
+      "lrt" = "eza --icons -lsnew";
+      "ls" = "eza";
+      "ll" = "eza --long --group";
+      "la" = "eza --all";
+
+    };
+  };
+}

users/steve/home/global.nix

@@ -0,0 +1,22 @@
+{ config, ... }:
+{
+  imports = [
+    ./cli
+    ./programs.nix
+    ./ssh_config.nix
+  ];
+
+  programs = {
+    home-manager.enable = true;
+    git.enable = true;
+  };
+
+  home = {
+    username = "steve";
+    homeDirectory = "/home/${config.home.username}";
+    stateVersion = "24.05";
+    sessionVariables = {
+      FLAKE = "$HOME/dotfiles";
+    };
+  };
+}

users/steve/home/programs.nix

@@ -0,0 +1,57 @@
+{ pkgs, ... }:
+{
+  home.packages = with pkgs; [
+    # cli
+    bat
+    btop
+    eza
+    fd
+    ffmpegthumbnailer
+    fzf
+    git
+    gnupg
+    imagemagick
+    jq
+    ncdu
+    ouch
+    p7zip
+    poppler
+    rar
+    ripgrep
+    starship
+    tmux
+    unzip
+    yazi
+    zoxide
+    # system info
+    hwloc
+    lynis
+    pciutils
+    smartmontools
+    usbutils
+    # networking
+    iperf3
+    nmap
+    wget
+    # python
+    poetry
+    ruff
+    uv
+    # nodejs
+    nodejs
+    # Rust packages
+    trunk
+    wasm-pack
+    cargo-watch
+    cargo-generate
+    cargo-audit
+    cargo-update
+    # nix
+    nix-init
+    nix-output-monitor
+    nix-prefetch
+    nix-tree
+    nixfmt
+    treefmt
+  ];
+}

users/steve/home/ssh_config.nix

@@ -0,0 +1,6 @@
+{
+  programs.ssh = {
+    enable = true;
+    enableDefaultConfig = false;
+  };
+}

users/steve/systems/bob.nix

@@ -0,0 +1,5 @@
+{
+  imports = [
+    ../home/global.nix
+  ];
+}

users/steve/systems/jeeves.nix

@@ -0,0 +1,5 @@
+{
+  imports = [
+    ../home/global.nix
+  ];
+}