Merge branch 'claude/blognames-table-design-0573fb' into master

Blogs.BlogId was a one-time copy of BlogNames and nothing kept it
current: 12,238 blogs first seen after 2026-08-07 had a BlogNames ID
but a NULL Blogs.BlogId, so GetBlogs' join on BlogId silently skipped
them and their 23,148 notes.

- retire-blognames.sql: stub Blogs rows for the 17 unregistered note
  participants, backfill IDs (none renumbered), make ix_Blogs_BlogId
  UNIQUE, drop BlogNames, and add triggers that stop a Blogs row with
  a BlogId from being deleted, renamed or renumbered
- AddNote registers both blogs via RegisterBlog (Blogs row + MAX+1 ID)
  and every query resolves names through Blogs instead of BlogNames
- verify-db-schema.sql reports a DB that still has BlogNames (1e)
- Update TL.db.md, AGENTS.md and the DB Browser saved queries

Co-Authored-By: Claude Opus 5.5 <[email protected]>

.github/copilot-instructions.md

@@ -31,7 +31,7 @@ dotnet run -- --test [blogname] [postID]  # Test API for specific post
 - `--test [blogname] [postID]`: Test API note collection
 - `--posts`: Export post blogs to file
 - `--blogs`: Export blog list to file
-- `--collect`: Collect notes for all posts in DB
+- `--collect [0|1] [datetime] [blogname]`: Collect notes for posts in DB. Optional `blogname` restricts the run to one blog (exact match), e.g. `--collect 1 zomb-eh`. Add `--force` to ignore the periodic re-collect cooldown so already-collected posts are re-queued immediately (mode 1 only). Add `--fromDate <datetime>` / `--toDate <datetime>` to only re-queue already-collected posts whose original PostDate is on/after / on/before that date (mode 1 only; either or both may be given; applies with or without `--force`)
 - `--blogsR`: Export reply blogs to file
 - `--blogsO [start] [stop]`: Export blogs within range
 

AGENTS.md

@@ -47,6 +47,48 @@ say nothing about the item being fetched, so they must not be recorded as per-it
 - Long-running commands return exit 3 when a pass ends incomplete (rate-limit pause, breaker trip, or
   skipped items), so a caller can distinguish that from a clean run
 
+### `Notes` Stores Integer IDs, Not Names
+As of 2026-08-07 `Notes.RootBlogName`, `NoteBlogName` and `Type` are gone, replaced by
+`RootBlogId`, `NoteBlogId` and `TypeId`. Blog IDs resolve through `Blogs.BlogId`, and
+types through the `NoteTypes` lookup table. There is no compatibility view: naming an old
+column is a hard SQLite error, so unlike `IsActive` this is a hard cut with no runtime
+probe. Full detail in `URLNotesGrabberCORE/TL.db.md`.
+
+- **`Blogs.BlogId` is the only blog-ID authority (since 2026-09-28).** IDs used to live in a
+  `BlogNames` table with an unmaintained copy in `Blogs.BlogId`. The copy drifted and hid
+  12k blogs from `GetBlogs`, so `retire-blognames.sql` moved the authority into `Blogs`
+  and **dropped `BlogNames` entirely**. There is no compatibility view, so naming it is
+  `no such table`. Do not recreate it
+- **Joining `Notes` to `Blogs`**: `FROM Blogs B INNER JOIN Notes N ON N.NoteBlogId = B.BlogId`
+- **Joining `Notes` to `Posts` also goes through `Blogs`**, since `Posts` has only
+  `BlogName`: `Posts P JOIN Blogs RB ON RB.BlogName = P.BlogName JOIN Notes N ON
+  N.RootBlogId = RB.BlogId` (`GetRepliesWithFilledText`)
+- **Resolve a name by filtering `Blogs`, never by scanning `Notes`**:
+  `WHERE NoteBlogId = (SELECT BlogId FROM Blogs WHERE BlogName = @name)`. The subquery is a
+  primary-key probe and does not show against the 1.2M-row table
+- **`AddNote` registers both blogs *and* the note type** before inserting, all in one
+  transaction. `RegisterBlog` does `INSERT OR IGNORE` into `Blogs`, then assigns
+  `BlogId = MAX(BlogId) + 1` where it is NULL. Unlike `AddBlog`, it does not skip `deact`
+  names, because a note by a deactivated blog still needs an ID. `NoteTypes` is a table
+  rather than a `CHECK` constraint precisely so an unseen type is an `INSERT`. Without
+  that registration a type would resolve to `NULL` and fail the `NOT NULL` on `TypeId`,
+  losing the note
+- **Assigning a `BlogId` is bookkeeping and must not move `DateModified`**
+- **`Blogs.BlogId` is NULL on ~166k of ~199k rows**, every blog that has never appeared in
+  a note. An inner join on it silently drops them. Correct for engagement queries, wrong
+  for anything listing the registry. `ix_Blogs_BlogId` is `UNIQUE`, which allows many NULLs
+- **IDs are stable and must never be renumbered.** They are stored in 1.2M `Notes` rows.
+  Triggers `trg_Blogs_BlogId_NoDelete` and `trg_Blogs_BlogId_Immutable` abort any
+  `DELETE` of a `Blogs` row that has a `BlogId`, and any change to its `BlogId` or
+  `BlogName`. A blog renamed upstream gets a new row. Remove a blog with `IsActive = 0`.
+  These triggers are also what make `MAX(BlogId) + 1` safe: no ID can ever be freed for
+  reuse
+- Prefer `TypeId = (SELECT TypeId FROM NoteTypes WHERE Type = 'reply')` over a hardcoded
+  ID. A negated `TypeId NOT IN (SELECT …)` is only correct because `TypeId` is `NOT NULL`
+- Duplicate-key detection uses `IsNotesDuplicateKey`, which matches the constraint and the
+  table rather than an exact column list. The old literal string comparison broke silently
+  on this rename — do not reintroduce one
+
 ### `IsActive` Is Not Ours To Write
 `Blogs.IsActive`, `Posts.IsActive` and `Notes.IsActive` are removal flags set by other tools
 (Rolodex). `0` means removed; anything else, including `NULL`, means live. Full detail in
@@ -67,6 +109,93 @@ say nothing about the item being fetched, so they must not be recorded as per-it
 - Do not add these columns from this app, and do not add them to the missing-column list in
   `verify-db-schema.sql`
 
+### `DateModified` Tracks Real Changes Only
+`Blogs.DateModified`, `Posts.DateModified` and `Notes.DateModified` must move only when a
+column beside `DateModified` itself actually changed. Re-crawling or re-ingesting identical
+content has to leave the row — and its timestamp — untouched, or downstream consumers cannot
+tell a refreshed row from a rewritten one.
+
+- Enforce it in the `WHERE` clause, not in C#. Every `UPDATE` that sets `DateModified` ends
+  with an `AND (<col> <> @param OR ...)` term covering every column in its `SET` list, so
+  SQLite matches zero rows on a no-op and never writes
+- Compare NULL-safely: `IFNULL(col, '') <> IFNULL(@param, '')` for text,
+  `IFNULL(col, 0) <> @param` for integer flags. A bare `col <> @param` is NULL on a NULL
+  column and silently skips the row that most needs writing
+- Where NULL is not equivalent to the default, spell it out. The `HasBeenOutput = 0` stamps
+  use `(HasBeenOutput IS NULL OR HasBeenOutput <> 0)` because the selection queries test
+  `HasBeenOutput = 0`, which a NULL would never match
+- Dynamic `SET` lists (`UpdatePostContentFields`) build the guard alongside the assignments
+  so the two lists cannot drift apart
+- These statements now return 0 rows for "found but unchanged" as well as "not found".
+  Callers that read `ExecuteNonQuery()` must not treat 0 as "row missing"
+
+**`Posts.NotesGatheredDateTime` is crawl bookkeeping, not content.** It moves on every
+`-collect` pass and says nothing about the post, so it must never move `DateModified` on its
+own. `UpdatePostMarkNotesCollected` still writes it every pass but wraps the timestamp in
+`DateModified = CASE WHEN IFNULL(HasNotesGathered, 0) <> 1 THEN @dateModified ELSE
+DateModified END` — SQLite evaluates `SET` expressions against the pre-`UPDATE` row, so only
+the flag flipping counts as a modification. Use this shape for any column that has to be
+refreshed unconditionally without being a change. `Blogs.LikesLastRefreshed` is the
+deliberate exception: a refresh pass is treated as a real event on the blog row.
+
+**`Blogs.DateAdded` is write-once.** `AddBlog`'s `INSERT` is the only place that sets it. A
+new post arriving for a known blog reopens `HasBeenOutput` but must leave `DateAdded` alone —
+a new post is not a new blog, and rewriting the column both destroys the registration date
+and makes every insert look like a change.
+
+**`"."` in a `Posts` content field means "not supplied", not "empty".** `ReblogRecord`
+(`TraverseDirectory`'s parser for the local `.txt` export tree) and the `--likes` API path
+both default every content field to the literal string `"."` when their source has no value
+for it, then pass that straight to `UpdatePost`. A blog with two export folders in different
+field formats (a duplicate `_2` folder, or a Tumblr export whose field set changed over time)
+sends one record with a real `Title`/`Tags`/`Slug` and another with those fields `"."`
+because that format never had a line for them — and without a guard, re-importing both on
+every run flips the row back and forth forever, bumping `DateModified` on every pass even
+though the true content never changes.
+
+- Every content column in `UpdatePost`'s `SET` list is guarded the same way `RootBlogName`/
+  `RootURL` already were: `col = CASE WHEN @col = '.' THEN col ELSE @col END`. A `"."`
+  parameter leaves the existing value alone instead of overwriting it
+- The change-detection `WHERE` clause carries the same exception —
+  `(@col <> '.' AND IFNULL(col, '') <> @col) OR ...` — so a `"."`-only difference does not
+  make the statement fire at all, and `DateModified` stays put
+- Deliberately narrow: only the literal `"."` is the sentinel. An explicit empty string from
+  a real record still overwrites, same as before this fix. `postID`, `BlogName`, `hasImage`,
+  `ByLikes` are not part of this convention and are unaffected
+- If a new content field is added to `Posts`/`UpdatePost`, decide explicitly whether its
+  source can legitimately supply `"."` as "field absent" before deciding whether it needs
+  the same `CASE` treatment — don't assume every column needs it
+
+**`--ingest` (`UpsertPostFromTextFile`) uses `NULL`, not `"."`, for the same "field absent"
+convention, and reconciling exactly this kind of duplicate IS the feature's job.**
+`IngestMode` strips a trailing `_N` from the folder name before it ever reaches
+`UpsertPostFromTextFile`, so a duplicate export folder collapses onto the same `BlogName` on
+purpose — the whole point is to merge multiple differently-formatted files for the same post
+into one row. `IngestMode.G(key)` returns `null` (not `"."`) when a field's line is absent
+from a given file, `LegacyPostsDbImporter` passes `null` straight from a `NULL` source column,
+and files are walked in raw filesystem enumeration order — never sorted — so which file's call
+lands last for a given `(BlogName, PostID)` is arbitrary.
+
+- Before the fix, the `UPDATE` branch set every column unconditionally, so whichever file
+  processed last for a `PostID` would null out every field its own record didn't carry —
+  silently erasing real `Title`/`Slug`/`Tags`/… another file had, the opposite of what
+  `--ingest` exists to do. This is worse than the `"."` case above: that one only caused
+  churn (the two writes canceled out); this one loses data, and which posts lose which
+  fields depends on filesystem enumeration order
+- Same shape of fix, `NULL` instead of `"."` as the sentinel: `col = CASE WHEN @col IS NULL
+  THEN col ELSE @col END` in the `SET` list, `(@col IS NOT NULL AND IFNULL(col, '') <> @col)
+  OR ...` in the change-detection
+- Same narrow rule: only `NULL` (the field's line was never present in this file) is the
+  sentinel. `G()` already distinguishes this from "present but blank" — a dictionary miss is
+  `null`, an empty value after the prefix is `""` — so an explicitly blank field still
+  overwrites
+- `HasImage` is **not** guarded and remains a known gap: `IngestMode` always computes a
+  concrete `bool` (defaulting `false` when a file has no `Has Image:` line), so there is no
+  way for this function to tell "this format says no image" from "this format doesn't report
+  it at all" without changing the parameter to `bool?` and threading that through
+  `IngestMode`/`LegacyPostsDbImporter`. Fix this the same way if `--ingest` is observed
+  downgrading a post's `HasImage` from `1` to `0`
+
 ### Testing
 - No existing test suite; use xUnit if adding tests
 - Test critical logic: `ApiKeyPool` init, color parsing, config persistence

RERUN.sqbpro

@@ -1,67 +1,415 @@
-<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/jim/Nextcloud/C#/URLNotesGrabberCORE/URLNotesGrabberCORE/TL.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="4305"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="Posts" custom_title="0" dock_id="4" table="4,5:mainPosts"/><dock_state state="000000ff00000000fd00000001000000020000077200000379fc0100000006fb000000160064006f0063006b00420072006f00770073006500310100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500320100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500330100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500350100000000000005f40000000000000000fb000000160064006f0063006b00420072006f00770073006500340100000000000007720000011700fffffffb000000160064006f0063006b00420072006f00770073006500340100000000000005f40000000000000000000007720000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="ApiKeyPoolMeta" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="29"/><column index="2" value="64"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Blogs" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="257"/><column index="2" value="95"/><column index="3" value="54"/><column index="4" value="156"/><column index="5" value="51"/><column index="6" value="71"/><column index="7" value="85"/><column index="8" value="156"/><column index="9" value="156"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Posts" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="28" mode="1"/></sort><column_widths><column index="1" value="241"/><column index="2" value="148"/><column index="3" value="126"/><column index="4" value="300"/><column index="5" value="75"/><column index="6" value="187"/><column index="7" value="159"/><column index="8" value="75"/><column index="9" value="300"/><column index="10" value="300"/><column index="11" value="78"/><column index="12" value="249"/><column index="13" value="300"/><column index="14" value="53"/><column index="15" value="300"/><column index="16" value="300"/><column index="17" value="41"/><column index="18" value="75"/><column index="19" value="96"/><column index="20" value="300"/><column index="21" value="96"/><column index="22" value="300"/><column index="23" value="300"/><column index="24" value="42"/><column index="25" value="60"/><column index="26" value="218"/><column index="27" value="920"/><column index="28" value="156"/><column index="29" value="156"/></column_widths><filter_values><column index="24" value="=1"/><column index="28" value="&gt;2026-05-06 20:00:01"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">UPDATE Posts
-SET HasNotesGathered = 0
-WHERE (BlogName, PostID) IN (
-    SELECT p.BlogName, p.PostID
-    FROM Posts p
-    WHERE p.HasNotesGathered = 1
-	AND P.notesGatheredDatetime &lt; 1774294520
-    AND EXISTS (
-        SELECT 1 
-        FROM Notes n 
-        WHERE n.PostID = p.PostID 
-          AND n.RootBlogName = p.BlogName
-          --AND n.Type NOT IN ('reblog', 'reply')
-    )
-    ORDER BY P.PostDate ASC
-    --LIMIT 500
-);</sql><sql name="Mark Blogs">select *
+<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/jim/Nextcloud/C#/URLNotesGrabberCORE/URLNotesGrabberCORE/TL.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="4486"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="Notes" custom_title="0" dock_id="4" table="4,5:mainNotes"/><dock_state state="000000ff00000000fd0000000100000002000005470000029efc0100000006fb000000160064006f0063006b00420072006f00770073006500310100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500320100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500330100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500350100000000000005f40000000000000000fb000000160064006f0063006b00420072006f00770073006500340100000000000005470000011100fffffffb000000160064006f0063006b00420072006f00770073006500340100000000000005f40000000000000000000005470000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="ApiKeyPoolMeta" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="29"/><column index="2" value="64"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Blogs" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="7" mode="1"/></sort><column_widths><column index="1" value="257"/><column index="2" value="108"/><column index="3" value="63"/><column index="4" value="156"/><column index="5" value="60"/><column index="6" value="81"/><column index="7" value="85"/><column index="8" value="156"/><column index="9" value="156"/><column index="10" value="151"/><column index="11" value="125"/><column index="12" value="129"/></column_widths><filter_values><column index="4" value="1"/><column index="7" value="&gt;2026-05-27 17:22:36"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Notes" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="4" mode="1"/></sort><column_widths><column index="1" value="81"/><column index="2" value="148"/><column index="3" value="83"/><column index="4" value="85"/><column index="5" value="56"/><column index="6" value="300"/><column index="7" value="156"/><column index="8" value="156"/><column index="9" value="156"/><column index="10" value="63"/></column_widths><filter_values><column index="2" value="4370"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Posts" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="14" mode="1"/></sort><column_widths><column index="1" value="241"/><column index="2" value="148"/><column index="3" value="126"/><column index="4" value="300"/><column index="5" value="75"/><column index="6" value="187"/><column index="7" value="159"/><column index="8" value="75"/><column index="9" value="0"/><column index="10" value="0"/><column index="11" value="0"/><column index="12" value="249"/><column index="13" value="300"/><column index="14" value="53"/><column index="15" value="300"/><column index="16" value="300"/><column index="17" value="41"/><column index="18" value="75"/><column index="19" value="96"/><column index="20" value="300"/><column index="21" value="96"/><column index="22" value="300"/><column index="23" value="300"/><column index="24" value="300"/><column index="25" value="60"/><column index="26" value="218"/><column index="27" value="300"/><column index="28" value="156"/><column index="29" value="156"/><column index="30" value="69"/><column index="31" value="63"/></column_widths><filter_values><column index="1" value="137735301451"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns><column index="9" value="1"/><column index="10" value="1"/><column index="11" value="1"/></hidden_columns><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="Mark Blogs">select *
 from Blogs
 --update blogs set HasBeenOutput = 1
 where HasBeenOutput = 0
 AND
 blogname in
-(
-'teaberrybee',
-'reddevilgoddesstoo',
-'waywardog13',
-'wzjustbrowsing-blog',
-'lewerta',
-'nudenymph',
-'caylachief'
-
-)</sql><sql name="New Notes">select RootBlogName, PostID, NoteBlogName || '.tumblr.com' as NoteBlogName, DatetimeCrawled, TimeStamp, type, RootBlogName || '.tumblr.com/post/' || postid, datetime(timestamp, 'unixepoch')
-from Notes
+('udontn33dh1m',
+'tyrantsxblood',
+'sentry-34',
+'deathcabforfrankie',
+'abheith-sasta',
+'kuwaiikittenghost',
+'kansasmud',
+'03diesel',
+'itzameallieee',
+'fireball-temptations',
+'mamaisamess',
+'906raised-and-dogobsessed',
+'the-queerist-wolf',
+'counting-corpsess',
+'aqueenbby',
+'maybememoriesx',
+'queenofnevers',
+'obsidian-psyche',
+'lilmissellexo',
+'alittlebunny95',
+'rage--and--grace',
+'savage-deniz',
+'daddyspuddleprincess',
+'littledefenstration',
+'bearded-snorlax',
+'thosesummerskiess',
+'tubadtoph',
+'lieutenant-dan-ice-cream',
+'brittvnybitch',
+'a-smol-gayologist',
+'sum1random',
+'samsternelly',
+'littlemouseylauren',
+'princessleiaorgasma',
+'bloodstaineddkisses',
+'letsfacerealitybabe',
+'x--marks--thespot',
+'space-and-suffering',
+'rinarootski',
+'thiccandtired',
+'fvcking-scvmbag',
+'fullblownwizard',
+'bigjewface',
+'unleash-the-krayken',
+'bumpintheroad',
+'liltexasjedii',
+'nawtydude',
+'queenpeachqueen',
+'the-clansman',
+'balmain-bxtch'
+)</sql><sql name="New Notes">select P.slug, N.replyText, rbn.BlogName as RootBlogName, n.PostID, nbn.BlogName || '.tumblr.com' as NoteBlogName, DatetimeCrawled, TimeStamp, nt.Type, rbn.BlogName || '.tumblr.com/post/' || n.postid, datetime(timestamp, 'unixepoch')
+from Notes N
+inner join Posts P on p.PostID = n.PostID
+inner join Blogs rbn on rbn.BlogId = n.RootBlogId
+inner join Blogs nbn on nbn.BlogId = n.NoteBlogId
+inner join NoteTypes nt on nt.TypeId = n.TypeId
 where
-DatetimeCrawled &gt; '2026-05-14 02:50:05' --and type like 'r%'
-order by DatetimeCrawled desc</sql><sql name="Pull Blogs*">SELECT distinct␍
-	'''' || blogname || ''',',
-	blogs.*
-	, blogname || '.tumblr.com'
-FROM
-	Blogs
-		inner JOIN
-	Notes on notes.noteBlogName = blogs.BlogName
-WHERE
-	HasBeenOutput = 0 and type = 'reblog'
-order by
-	Notes.Type desc,
-	DateAdded desc
-LIMIT 100;</sql><sql name="SQL 7">WITH ReplyCounts AS (
+DatetimeCrawled &gt; '2026-08-07 11:47:22' and nt.Type like 'r%'
+and P.IsActive = 1
+order by n.DatetimeCrawled</sql><sql name="SQL 7">WITH ReplyCounts AS (
     SELECT
-        NoteBlogName, 
+        NoteBlogId,
         COUNT(DISTINCT replyText) AS DistinctReplyCount
     FROM Notes
 	where replyText &lt;&gt; '.'
-    GROUP BY NoteBlogName
+    GROUP BY NoteBlogId
 )
 SELECT
-    n.RootBlogName || '.tumblr.com/post/' || n.PostID AS PostURL, postid,
-    n.NoteBlogName,
+    rbn.BlogName || '.tumblr.com/post/' || n.PostID AS PostURL, postid,
+    nbn.BlogName AS NoteBlogName,
     n.replyText,
     c.DistinctReplyCount
 FROM Notes n
-JOIN ReplyCounts c ON n.NoteBlogName = c.NoteBlogName
-where replyText &lt;&gt; '.' and type &lt;&gt; 'reply'
---AND N.NoteBlogName NOT IN ( 'roadblocker21', 'thesaddemon666', 'edwardabbeyhoffman', 'tattedsoldier20', 'zomb-eh', 'animalistic13', 'indken', 'maccloud1592',
+JOIN ReplyCounts c ON n.NoteBlogId = c.NoteBlogId
+JOIN Blogs rbn ON rbn.BlogId = n.RootBlogId
+JOIN Blogs nbn ON nbn.BlogId = n.NoteBlogId
+JOIN NoteTypes t ON t.TypeId = n.TypeId
+where replyText &lt;&gt; '.' and t.Type &lt;&gt; 'reply'
+--AND nbn.BlogName NOT IN ( 'roadblocker21', 'thesaddemon666', 'edwardabbeyhoffman', 'tattedsoldier20', 'zomb-eh', 'animalistic13', 'indken', 'maccloud1592',
 --'moss-wizard', 'supertrucker12682', 'exploringthrupics', 'padeyepete' )
-order by c.DistinctReplyCount desc, n.NoteBlogName, n.DateModified desc, replyText, RootBlogName, PostID</sql><current_tab id="3"/></tab_sql></sqlb_project>
+order by c.DistinctReplyCount desc, nbn.BlogName, n.DateModified desc, replyText, rbn.BlogName, PostID</sql><sql name="Collect">WITH PostsWithCount AS ( SELECT P.BlogName, P.PostID, 1925013599 AS LatestNoteTimestamp, P.NotesGatheredDateTime, COUNT(P.PostID) OVER(PARTITION BY P.BlogName) AS CNT, P.HasNotesGathered, P.NotFound, P.PostDate FROM Posts P WHERE COALESCE(P.IsActive, 1) = 1 ), Unioned AS ( SELECT BlogName, PostID, LatestNoteTimestamp, NotesGatheredDateTime, CNT, PostDate FROM PostsWithCount WHERE NotFound = 0 AND HasNotesGathered = 0 UNION SELECT BlogName, PostID, LatestNoteTimestamp, NotesGatheredDateTime, CNT, PostDate FROM PostsWithCount WHERE BlogName = 'zomb-eh' AND NotFound = 0 AND NotesGatheredDateTime &lt; unixepoch('now', 'localtime', '-3 days') ) SELECT U.BlogName, U.PostID, U.LatestNoteTimestamp, U.NotesGatheredDateTime, U.CNT FROM Unioned U WHERE (U.NotesGatheredDateTime &lt; 1787237598 OR U.NotesGatheredDateTime IS NULL) ORDER BY U.NotesGatheredDateTime, U.PostDate DESC, U.BlogName, U.PostID;</sql><sql name="Del Posts">delete from posts where postid in 
+(
+'741662499571728384',
+178892849664,
+178264721139,
+177012868749,
+169950081964,
+755440787056099328
+)</sql><sql name="notes NO post">select *
+-- delete
+from notes
+where postid not in (select distinct postid from posts where IsActive = 1)</sql><sql name="Pull Blogs">-- ============================================================================
+-- blogs-added-after-august-2026-with-reblog-or-reply.sql
+--
+-- Purpose: Of all blogs in Blogs, find the ones that show up in Notes as the
+--          engager (NoteBlogId) on a 'reblog' or 'reply' note, sorted by
+--          DateAdded. (Originally scoped to &quot;added after August 2026&quot; --
+--          that cutoff is now removed per request; QUERY 2 shows how to put
+--          a date floor back if needed.)
+--
+-- Read-only. No INSERT/UPDATE/DELETE/DDL anywhere in this file.
+--
+-- How to use (DB Browser for SQLite):
+--   1. File &gt; Open Database -&gt; TL.db
+--   2. Execute SQL tab. Each QUERY below is independent; Ctrl+Enter runs just
+--      the one your cursor is in.
+--
+-- The join, once:
+--   &quot;Added after August 2026&quot; filters Blogs.DateAdded. &quot;Has a reblog/reply
+--   note&quot; means the blog is the engager, which is NoteBlogId -- not
+--   RootBlogId, which is the blog that *owns* the post being reacted to
+--   (see find-notes-on-inactive-posts.sql for that side). Per TL.db.md, the
+--   Blogs&lt;-&gt;Notes join is a single integer hop and should not be routed
+--   through Blogs:
+--       Blogs.BlogId = Notes.NoteBlogId
+--   EXISTS is used rather than a JOIN so a blog with many qualifying notes
+--   still contributes one output row.
+--
+-- Excluding notes on an inactive post: same shape as
+--   find-notes-on-inactive-posts.sql -- Notes only carries RootBlogId (an
+--   integer), so reaching Posts.IsActive needs the one text hop the rest of
+--   this file avoids: RootBlogId -&gt; Blogs.BlogName = Posts.BlogName,
+--   matched on PostID. It's a LEFT JOIN, not an inner one: only 3,867 of
+--   20,430 root/engager blogs have any stored Posts rows at all (TL.db.md),
+--   so most reblog/reply notes have no Posts row to check and must be kept,
+--   not dropped by an inner join. COALESCE(p.IsActive, 1) = 1 keeps a note
+--   unless its post is explicitly IsActive = 0 -- NULL (no Posts row, or a
+--   stored row with no flag written) means live, per the schema's own
+--   convention (TL.db.md, &quot;Posts.IsActive and Notes.IsActive&quot;). This is a
+--   big filter in practice: of the blogs that qualified before it, most
+--   have every one of their reblog/reply notes pointing at a since-removed
+--   post, not just some -- verified against the live data, not assumed.
+--
+-- On DateAdded: this column is not written consistently -- most rows hold
+--   ISO 'yyyy-MM-dd HH:mm:ss', but 17k+ hold US 'M/d/yy' from a 2025 bulk
+--   import (see TL.db.md, &quot;DateAdded is not written consistently&quot;). As text,
+--   those two shapes do not sort or compare against each other correctly, so
+--   QUERY 0 normalises both to an ISO date before filtering. In the live data
+--   every US-format row predates August 2026 anyway (only '12/23/25' and
+--   '12/24/25' occur), so this makes no difference to the current answer --
+--   it's here so the query stays correct if that ever changes.
+-- ============================================================================
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 0 / THE ANSWER -- one row per qualifying blog, sorted by DateAdded
+--   descending (normalised -- see the note above). No date cutoff, but now
+--   scoped to HasBeenOutput = 0 AND IsActive = 1. 4,739 rows in the live
+--   data.
+--
+--   earliest_reblog_or_reply_utc is the MIN(TimeStamp) among this blog's
+--   reblog-or-reply notes (either type counts -- see the column name).
+--   Getting this meant switching QUERY 0 from EXISTS to an inner JOIN +
+--   GROUP BY: EXISTS can only tell you a qualifying row is present, not
+--   aggregate over which ones. No CASE is needed inside the MIN() because
+--   the WHERE below already restricts the joined rows to reblog/reply, so
+--   every row a blog brings into the aggregate is one this column should
+--   consider. A blog appears exactly once, same as before, and this column
+--   is never NULL for a row that's in the result at all (an earlier
+--   revision aggregated reblog only, which left it NULL for the 181 blogs
+--   that had replies but no reblogs).
+-- ----------------------------------------------------------------------------
+WITH BlogsSplit AS (
+    SELECT
+        b.BlogId,
+        b.BlogName,
+        b.DateAdded,
+        CASE WHEN b.DateAdded LIKE '____-__-__%' THEN 1 ELSE 0 END AS IsIso,
+        -- for the US 'M/d/yy' shape only: everything after the first '/'
+        substr(b.DateAdded, instr(b.DateAdded, '/') + 1) AS RestAfterMonth
+      FROM Blogs b
+     WHERE b.BlogId IS NOT NULL and HasBeenOutput = 0 and IsActive = 1  -- a blog can only match Notes if it has one
+),
+BlogsNorm AS (
+    SELECT
+        BlogId,
+        BlogName,
+        DateAdded,
+        CASE
+          WHEN IsIso = 1 THEN date(DateAdded)
+          ELSE date(
+                 '20' || substr(RestAfterMonth, instr(RestAfterMonth, '/') + 1) || '-' ||
+                 substr('00' || substr(DateAdded, 1, instr(DateAdded, '/') - 1), -2) || '-' ||
+                 substr('00' || substr(RestAfterMonth, 1, instr(RestAfterMonth, '/') - 1), -2)
+               )
+        END AS DateAddedNorm
+      FROM BlogsSplit
+)
+SELECT
+    bn.BlogId,
+    bn.BlogName,
+    bn.DateAdded,
+    bn.DateAddedNorm,
+    datetime(MIN(n.TimeStamp), 'unixepoch') AS earliest_reblog_or_reply_utc
+  FROM BlogsNorm bn
+  JOIN Notes      n       ON n.NoteBlogId   = bn.BlogId
+  JOIN NoteTypes  t       ON t.TypeId       = n.TypeId
+  JOIN Blogs  root_bn ON root_bn.BlogId = n.RootBlogId
+  LEFT JOIN Posts p       ON p.BlogName     = root_bn.BlogName
+                         AND p.PostID       = n.PostID
+ WHERE t.Type IN ('reblog')--, 'reply')
+   AND COALESCE(p.IsActive, 1) = 1   -- exclude notes on a post explicitly marked removed
+ GROUP BY bn.BlogId, bn.BlogName, bn.DateAdded, bn.DateAddedNorm
+ ORDER BY bn.DateAddedNorm desc;
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 1 -- same answer, with a per-blog breakdown of which type(s) fired
+--   and how many. Useful once QUERY 0 has rows; redundant while it's empty.
+-- ----------------------------------------------------------------------------
+-- WITH BlogsSplit AS ( ... ), BlogsNorm AS ( ... )  -- reuse the CTEs above
+--
+-- SELECT
+--     bn.BlogId,
+--     bn.BlogName,
+--     bn.DateAddedNorm,
+--     SUM(CASE WHEN t.Type = 'reblog' THEN 1 ELSE 0 END) AS reblog_count,
+--     SUM(CASE WHEN t.Type = 'reply'  THEN 1 ELSE 0 END) AS reply_count
+--   FROM BlogsNorm bn
+--   JOIN Notes     n ON n.NoteBlogId = bn.BlogId
+--   JOIN NoteTypes t ON t.TypeId     = n.TypeId
+--  WHERE t.Type IN ('reblog', 'reply')
+--  GROUP BY bn.BlogId, bn.BlogName, bn.DateAddedNorm
+--  ORDER BY bn.DateAddedNorm;
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 2 -- put a date floor back, if wanted later.
+--   Same as QUERY 0, with one extra line in the outer WHERE:
+--       AND bn.DateAddedNorm &gt;= '2026-09-01'   -- or whatever cutoff
+-- ----------------------------------------------------------------------------
+-- ============================================================================
+-- blogs-added-after-august-2026-with-reblog-or-reply.sql
+--
+-- Purpose: Of all blogs in Blogs, find the ones that show up in Notes as the
+--          engager (NoteBlogId) on a 'reblog' or 'reply' note, sorted by
+--          DateAdded. (Originally scoped to &quot;added after August 2026&quot; --
+--          that cutoff is now removed per request; QUERY 2 shows how to put
+--          a date floor back if needed.)
+--
+-- Read-only. No INSERT/UPDATE/DELETE/DDL anywhere in this file.
+--
+-- How to use (DB Browser for SQLite):
+--   1. File &gt; Open Database -&gt; TL.db
+--   2. Execute SQL tab. Each QUERY below is independent; Ctrl+Enter runs just
+--      the one your cursor is in.
+--
+-- The join, once:
+--   &quot;Added after August 2026&quot; filters Blogs.DateAdded. &quot;Has a reblog/reply
+--   note&quot; means the blog is the engager, which is NoteBlogId -- not
+--   RootBlogId, which is the blog that *owns* the post being reacted to
+--   (see find-notes-on-inactive-posts.sql for that side). Per TL.db.md, the
+--   Blogs&lt;-&gt;Notes join is a single integer hop and should not be routed
+--   through Blogs:
+--       Blogs.BlogId = Notes.NoteBlogId
+--   EXISTS is used rather than a JOIN so a blog with many qualifying notes
+--   still contributes one output row.
+--
+-- Excluding notes on an inactive post: same shape as
+--   find-notes-on-inactive-posts.sql -- Notes only carries RootBlogId (an
+--   integer), so reaching Posts.IsActive needs the one text hop the rest of
+--   this file avoids: RootBlogId -&gt; Blogs.BlogName = Posts.BlogName,
+--   matched on PostID. It's a LEFT JOIN, not an inner one: only 3,867 of
+--   20,430 root/engager blogs have any stored Posts rows at all (TL.db.md),
+--   so most reblog/reply notes have no Posts row to check and must be kept,
+--   not dropped by an inner join. COALESCE(p.IsActive, 1) = 1 keeps a note
+--   unless its post is explicitly IsActive = 0 -- NULL (no Posts row, or a
+--   stored row with no flag written) means live, per the schema's own
+--   convention (TL.db.md, &quot;Posts.IsActive and Notes.IsActive&quot;). This is a
+--   big filter in practice: of the blogs that qualified before it, most
+--   have every one of their reblog/reply notes pointing at a since-removed
+--   post, not just some -- verified against the live data, not assumed.
+--
+-- On DateAdded: this column is not written consistently -- most rows hold
+--   ISO 'yyyy-MM-dd HH:mm:ss', but 17k+ hold US 'M/d/yy' from a 2025 bulk
+--   import (see TL.db.md, &quot;DateAdded is not written consistently&quot;). As text,
+--   those two shapes do not sort or compare against each other correctly, so
+--   QUERY 0 normalises both to an ISO date before filtering. In the live data
+--   every US-format row predates August 2026 anyway (only '12/23/25' and
+--   '12/24/25' occur), so this makes no difference to the current answer --
+--   it's here so the query stays correct if that ever changes.
+-- ============================================================================
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 0 / THE ANSWER -- one row per qualifying blog, sorted by
+--   earliest_reblog_or_reply_utc then DateAdded descending (normalised --
+--   see the note above). No date cutoff, but scoped to HasBeenOutput = 0
+--   AND IsActive = 1, and now excluding notes on a removed post (see the
+--   header note above). 1,804 rows in the live data as of this revision --
+--   down from 4,396 just before this exclusion was added, because most of
+--   the blogs that dropped out had *every* reblog/reply note pointing at a
+--   now-inactive post, not just some (the number moves between runs
+--   regardless -- crawling and output flip HasBeenOutput/IsActive on live
+--   rows).
+--
+--   earliest_reblog_or_reply_utc is the earliest TimeStamp among this
+--   blog's reblog-or-reply notes (either type counts -- see the column
+--   name); earliest_reblog_or_reply_postid and _root_blogid identify that
+--   specific note's post: PostID + RootBlogId together, not PostID alone --
+--   see TL.db.md (&quot;345 post IDs exist under more than one blog&quot;), same
+--   caution as in find-notes-on-inactive-posts.sql. Resolve RootBlogId to a
+--   name via Blogs (or Blogs, tolerating a miss) if you need it.
+--
+--   Getting &quot;which note&quot; rather than just &quot;when&quot; doesn't fit a plain
+--   MIN()/GROUP BY -- an aggregate can tell you the earliest value but not
+--   which row it came from. EarliestNote instead ranks each blog's
+--   reblog/reply notes with ROW_NUMBER() OVER (PARTITION BY NoteBlogId
+--   ORDER BY TimeStamp), and QUERY 0 takes rn = 1. The ORDER BY carries a
+--   PostID tiebreak because (NoteBlogId, TimeStamp) is not unique in this
+--   data -- ties exist (e.g. NoteBlogId 12 has 7 notes at the same
+--   TimeStamp) -- so without a tiebreak the &quot;earliest&quot; pick would be
+--   arbitrary among ties rather than deterministic.
+--
+--   EarliestNote also excludes notes on an inactive post before ranking
+--   (see the header note above), so &quot;earliest&quot; means earliest surviving
+--   note, not earliest overall -- a blog whose true-earliest note pointed
+--   at a since-removed post now surfaces its next-earliest live one
+--   instead. Applying the exclusion here, not as a filter on QUERY 0's
+--   final rows, matters: filtering after ROW_NUMBER would have picked the
+--   removed-post note as rn = 1 and then dropped the whole row instead of
+--   promoting the next candidate.
+-- ----------------------------------------------------------------------------
+WITH BlogsSplit AS (
+    SELECT
+        b.BlogId,
+        b.BlogName,
+        b.DateAdded,
+        CASE WHEN b.DateAdded LIKE '____-__-__%' THEN 1 ELSE 0 END AS IsIso,
+        -- for the US 'M/d/yy' shape only: everything after the first '/'
+        substr(b.DateAdded, instr(b.DateAdded, '/') + 1) AS RestAfterMonth
+      FROM Blogs b
+     WHERE b.BlogId IS NOT NULL and HasBeenOutput = 0 and IsActive = 1  -- a blog can only match Notes if it has one
+),
+BlogsNorm AS (
+    SELECT
+        BlogId,
+        BlogName,
+        DateAdded,
+        CASE
+          WHEN IsIso = 1 THEN date(DateAdded)
+          ELSE date(
+                 '20' || substr(RestAfterMonth, instr(RestAfterMonth, '/') + 1) || '-' ||
+                 substr('00' || substr(DateAdded, 1, instr(DateAdded, '/') - 1), -2) || '-' ||
+                 substr('00' || substr(RestAfterMonth, 1, instr(RestAfterMonth, '/') - 1), -2)
+               )
+        END AS DateAddedNorm
+      FROM BlogsSplit
+),
+EarliestNote AS (
+    SELECT
+        n.NoteBlogId,
+        n.RootBlogId,
+        n.PostID,
+        n.TimeStamp,
+        ROW_NUMBER() OVER (
+            PARTITION BY n.NoteBlogId
+            ORDER BY n.TimeStamp ASC, n.PostID ASC
+        ) AS rn
+      FROM Notes n
+      JOIN NoteTypes t       ON t.TypeId       = n.TypeId
+      JOIN Blogs root_bn ON root_bn.BlogId = n.RootBlogId
+      LEFT JOIN Posts p      ON p.BlogName     = root_bn.BlogName
+                            AND p.PostID       = n.PostID
+     WHERE t.Type IN ('reblog')--, 'reply')
+       AND n.NoteBlogId IN (SELECT BlogId FROM BlogsNorm)  -- scope the window to blogs we care about
+       AND COALESCE(p.IsActive, 1) = 1                     -- exclude notes on a post explicitly marked removed
+)
+SELECT
+    bn.BlogId,
+    bn.BlogName || '.tumblr.com',
+    '''' || bn.blogname || ''',',
+    bn.DateAdded,
+    bn.DateAddedNorm,
+    datetime(en.TimeStamp, 'unixepoch') AS earliest_reblog_or_reply_utc,
+    en.PostID                           AS earliest_reblog_or_reply_postid,
+    en.RootBlogId                       AS earliest_reblog_or_reply_root_blogid
+  FROM BlogsNorm bn
+  JOIN EarliestNote en ON en.NoteBlogId = bn.BlogId AND en.rn = 1
+ ORDER BY earliest_reblog_or_reply_utc, bn.DateAddedNorm desc
+ limit 50;
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 1 -- same answer, with a per-blog breakdown of which type(s) fired
+--   and how many. Useful once QUERY 0 has rows; redundant while it's empty.
+-- ----------------------------------------------------------------------------
+-- WITH BlogsSplit AS ( ... ), BlogsNorm AS ( ... )  -- reuse the CTEs above
+--
+-- SELECT
+--     bn.BlogId,
+--     bn.BlogName,
+--     bn.DateAddedNorm,
+--     SUM(CASE WHEN t.Type = 'reblog' THEN 1 ELSE 0 END) AS reblog_count,
+--     SUM(CASE WHEN t.Type = 'reply'  THEN 1 ELSE 0 END) AS reply_count
+--   FROM BlogsNorm bn
+--   JOIN Notes     n ON n.NoteBlogId = bn.BlogId
+--   JOIN NoteTypes t ON t.TypeId     = n.TypeId
+--  WHERE t.Type IN ('reblog', 'reply')
+--  GROUP BY bn.BlogId, bn.BlogName, bn.DateAddedNorm
+--  ORDER BY bn.DateAddedNorm;
+
+
+-- ----------------------------------------------------------------------------
+-- QUERY 2 -- put a date floor back, if wanted later.
+--   Same as QUERY 0, with one extra line in the outer WHERE:
+--       AND bn.DateAddedNorm &gt;= '2026-09-01'   -- or whatever cutoff
+-- ----------------------------------------------------------------------------
+</sql><current_tab id="0"/></tab_sql></sqlb_project>

TL.sqbpro

@@ -9,16 +9,22 @@ WHERE
 ORDER BY
 	postdate desc</sql><sql name="SQL 2*">SELECT
 	datetime(TimeStamp, 'unixepoch'),
-	RootBlogName || '.tumblr.com/post/' || N.postid,
+	rbn.BlogName || '.tumblr.com/post/' || N.postid,
 	*,
-	NoteBlogName || '.tumblr.com'
+	nbn.BlogName || '.tumblr.com'
 FROM
 	Notes N
 	inner JOIN
-	Posts P on P.PostID = N.PostID and P.BlogName = N.RootBlogName
-WHERE	RootBlogName NOT IN ('xlittle-ghost', 'glimmerin-darlin', 'vvenus-child')␍
-		and type like 'r%'␍
-		and RootBlogName = 'zomb-eh'␍
+	Blogs rbn on rbn.BlogId = N.RootBlogId
+	inner JOIN
+	Blogs nbn on nbn.BlogId = N.NoteBlogId
+	inner JOIN
+	NoteTypes t on t.TypeId = N.TypeId
+	inner JOIN
+	Posts P on P.PostID = N.PostID and P.BlogName = rbn.BlogName
+WHERE	rbn.BlogName NOT IN ('xlittle-ghost', 'glimmerin-darlin', 'vvenus-child')
+		and t.Type like 'r%'
+		and rbn.BlogName = 'zomb-eh'
 		and P.HasImage = 1
 ORDER BY
 	TimeStamp desc</sql><current_tab id="1"/></tab_sql></sqlb_project>

URLNotesGrabberCORE/IngestMode.cs

@@ -85,7 +85,25 @@ namespace URLNotesGrabberCORE
                     {
                         string rawBlogName = Path.GetFileName(Path.GetDirectoryName(file) ?? "unknown");
                         string blogName = Regex.Replace(rawBlogName, @"_\d+$", "");
-                        string postType = Path.GetFileNameWithoutExtension(file);
+
+                        // The filename becomes the row's PostType, and PostType later becomes an
+                        // output filename -- so an unrecognized name here would mint a new type and
+                        // a new file from any stray .txt that happens to sit in the tree. Only the
+                        // eight real export files are ingestable.
+                        //
+                        // This is also what breaks the Unknown.txt cycle: OutputMode used to write
+                        // untyped rows to Unknown.txt, and this scan would read it straight back
+                        // and stamp those rows with the literal type "Unknown", making the file
+                        // regenerate itself forever.
+                        string? resolvedPostType = PostTypes.FromFileName(file);
+                        if (resolvedPostType == null)
+                        {
+                            filesSkipped++;
+                            continue;
+                        }
+                        // Non-nullable from here so the local Flush() below stays warning-clean:
+                        // nullable flow analysis does not reach into local functions.
+                        string postType = resolvedPostType;
 
                         if (targetBlog != null && !string.Equals(blogName, targetBlog, StringComparison.OrdinalIgnoreCase))
                         {

URLNotesGrabberCORE/LegacyPostsDbImporter.cs

@@ -29,6 +29,8 @@ namespace URLNotesGrabberCORE
             Console.WriteLine($"Reading legacy posts.db: {legacyDbPath}");
 
             int blogsCopied = 0;
+            int blogPathsWritten = 0;
+            int blogsWithoutPath = 0;
             int postsUpserted = 0;
             int errors = 0;
 
@@ -48,7 +50,13 @@ namespace URLNotesGrabberCORE
                         if (string.IsNullOrWhiteSpace(blogName)) continue;
                         try
                         {
-                            DataAccess.SetBlogTTFolderPath(blogName, ttFolderPath);
+                            // A legacy row whose TTFolderPath was already NULL copies nothing.
+                            // Counting it as "copied" is what hid the fact that this import has
+                            // never populated a single path.
+                            if (string.IsNullOrWhiteSpace(ttFolderPath))
+                                blogsWithoutPath++;
+                            else if (DataAccess.SetBlogTTFolderPath(blogName, ttFolderPath.Trim()))
+                                blogPathsWritten++;
                             blogsCopied++;
                         }
                         catch (Exception ex)
@@ -58,7 +66,7 @@ namespace URLNotesGrabberCORE
                         }
                     }
                 }
-                Console.WriteLine($"  Blogs copied: {blogsCopied}");
+                Console.WriteLine($"  Blogs seen: {blogsCopied}, TTFolderPath written: {blogPathsWritten}, legacy rows with no path: {blogsWithoutPath}");
 
                 // 2) Copy Posts
                 try
@@ -109,7 +117,12 @@ namespace URLNotesGrabberCORE
                                 question: reader.IsDBNull(18) ? null : reader.GetString(18),
                                 answer: reader.IsDBNull(19) ? null : reader.GetString(19),
                                 title: reader.IsDBNull(20) ? null : reader.GetString(20),
-                                postType: reader.IsDBNull(21) ? null : reader.GetString(21),
+                                // A legacy Posts.db predating the PostType column hands back NULL
+                                // here, and on the INSERT branch that NULL is stored -- reseeding
+                                // exactly the untyped rows the backfill exists to clear. Normalize
+                                // so an unrecognized legacy value cannot become a filename either;
+                                // the backfill types whatever comes through as null.
+                                postType: PostTypes.Normalize(reader.IsDBNull(21) ? null : reader.GetString(21)),
                                 hasImage: hasImage);
                             postsUpserted++;
                             if (postsUpserted % 500 == 0)
@@ -138,7 +151,8 @@ namespace URLNotesGrabberCORE
             }
 
             Console.WriteLine($"\n========== Legacy import summary ==========");
-            Console.WriteLine($"Blogs copied:    {blogsCopied}");
+            Console.WriteLine($"Blogs seen:      {blogsCopied}");
+            Console.WriteLine($"Paths written:   {blogPathsWritten} (legacy rows with no path: {blogsWithoutPath})");
             Console.WriteLine($"Posts upserted:  {postsUpserted}");
             Console.WriteLine($"Errors:          {errors}");
             return errors == 0 ? 0 : 2;

URLNotesGrabberCORE/OutputMode.cs

@@ -8,28 +8,51 @@ namespace URLNotesGrabberCORE
     // field order). Reads from TL.db via DataAccess.GetAllPostsForBlog.
     public static class OutputMode
     {
-        public static int Run(IConfiguration config)
+        public static int Run(IConfiguration config, string[]? args = null)
         {
             DataAccess.EnsureTTFileHelperColumnsExist();
 
-            var blogs = DataAccess.GetAllBlogsWithTTFolderPath();
-            Console.WriteLine($"Found {blogs.Count} blog(s) to process.");
+            string dbPath = DataAccess.GetActiveDbPath();
+            Console.WriteLine($"Database: {Path.GetFullPath(dbPath)}");
 
-            foreach (var (blogName, ttFolderPath) in blogs)
+            if (!RefreshPaths(config, args ?? Array.Empty<string>()))
+                return 1;
+
+            var blogs = DataAccess.GetAllBlogsWithTTFolderPath();
+            int activeBlogs = DataAccess.CountActiveBlogs();
+            Console.WriteLine($"{blogs.Count} of {activeBlogs} active blog(s) have a TTFolderPath.");
+
+            if (blogs.Count == 0)
+            {
+                Console.WriteLine($"\nNothing to export: no blog in {Path.GetFullPath(dbPath)} has a TTFolderPath.");
+                Console.WriteLine("Point --output at a TumblThree root so it can populate them: --output <root>,");
+                Console.WriteLine("or set appSettings:PathTTRoot so the refresh runs automatically.");
+                return 1;
+            }
+
+            int missingFolderCount = 0;
+            int writtenCount = 0;
+
+            foreach (var (blogName, folder) in blogs)
             {
                 Console.WriteLine($"\nProcessing blog: {blogName}");
 
-                if (string.IsNullOrWhiteSpace(ttFolderPath) || !Directory.Exists(ttFolderPath))
+                // A stored path that this machine cannot see means the value was written on
+                // another machine -- re-running --updatepaths locally is the fix, so say so
+                // rather than lumping it in with "not set".
+                if (!Directory.Exists(folder))
                 {
-                    Console.WriteLine($"  TTFolderPath does not exist or is not set. Skipping.");
+                    Console.WriteLine($"  TTFolderPath folder not found: {folder}. Skipping.");
+                    missingFolderCount++;
                     continue;
                 }
 
-                Console.WriteLine($"  TTFolderPath: {ttFolderPath}");
+                Console.WriteLine($"  TTFolderPath: {folder}");
+                writtenCount++;
 
                 try
                 {
-                    foreach (var bakFile in Directory.GetFiles(ttFolderPath, "*.bak"))
+                    foreach (var bakFile in Directory.GetFiles(folder, "*.bak"))
                         File.Delete(bakFile);
                 }
                 catch (Exception ex)
@@ -37,16 +60,26 @@ namespace URLNotesGrabberCORE
                     Console.WriteLine($"  Error deleting .bak files: {ex.Message}");
                 }
 
-                RenameExistingTxtFilesToBak(ttFolderPath);
+                RenameExistingTxtFilesToBak(folder);
 
                 var posts = DataAccess.GetAllPostsForBlog(blogName);
                 Console.WriteLine($"  Found {posts.Count} post(s) for this blog.");
 
-                var grouped = posts.GroupBy(p => p.PostType ?? "Unknown");
+                // A post's type becomes a filename, so only a recognized type may be written. The
+                // old `PostType ?? "Unknown"` invented Unknown.txt for untyped rows, which --ingest
+                // then read back as a type named "Unknown" -- the two regenerated each other.
+                // Untyped rows are skipped instead: after the backfill these are only rows with no
+                // content signal at all, so nothing meaningful is lost, and nothing is invented.
+                var typed = posts.Where(p => PostTypes.Normalize(p.PostType) != null).ToList();
+                int untyped = posts.Count - typed.Count;
+                if (untyped > 0)
+                    Console.WriteLine($"    Skipping {untyped} post(s) with no recognized PostType.");
+
+                var grouped = typed.GroupBy(p => PostTypes.Normalize(p.PostType)!);
                 foreach (var typeGroup in grouped)
                 {
-                    string postType = typeGroup.Key ?? "Unknown";
-                    string outputFilePath = Path.Combine(ttFolderPath, $"{postType}.txt");
+                    string postType = typeGroup.Key;
+                    string outputFilePath = Path.Combine(folder, $"{postType}.txt");
                     var ordered = typeGroup.OrderBy(p => p.Date).ToList();
                     Console.WriteLine($"    Writing {ordered.Count} post(s) to {postType}.txt");
 
@@ -65,10 +98,57 @@ namespace URLNotesGrabberCORE
                 }
             }
 
-            Console.WriteLine("\nOutput mode complete.");
+            Console.WriteLine($"\nOutput mode complete. {writtenCount} blog(s) exported, {missingFolderCount} skipped for a missing folder.");
+
+            if (writtenCount == 0)
+                Console.WriteLine("Every TTFolderPath points at a folder this machine cannot see. The paths were most likely written on another machine -- re-run --updatepaths <root> here so they match local drive letters.");
+
             return 0;
         }
 
+        // Re-reads the TumblThree Index metadata into Blogs.TTFolderPath before exporting.
+        // A TL.db synced between machines cannot hold one absolute path that is valid on
+        // both, so the stored paths are only trustworthy on the machine that wrote them --
+        // which makes this refresh part of a normal export rather than a separate chore.
+        // Returns false only when the run should stop.
+        private static bool RefreshPaths(IConfiguration config, string[] args)
+        {
+            var settings = config.GetSection("appSettings");
+
+            if (args.Any(a => string.Equals(a, "--norefresh", StringComparison.OrdinalIgnoreCase)))
+            {
+                Console.WriteLine("Path refresh skipped (--norefresh); exporting to whatever paths TL.db already holds.");
+                return true;
+            }
+
+            string? root = args.FirstOrDefault(a => !a.StartsWith("--", StringComparison.Ordinal))
+                           ?? settings.GetValue<string>("PathTTRoot");
+
+            var result = UpdateBlogPathsRunner.Scan(root, verbose: false);
+
+            switch (result.Outcome)
+            {
+                case UpdateBlogPathsRunner.ScanOutcome.NoRootConfigured:
+                    Console.WriteLine("No TumblThree root configured (appSettings:PathTTRoot is empty and none was passed),");
+                    Console.WriteLine("so TTFolderPath was not refreshed. Pass one as --output <root> to refresh it.");
+                    return true;
+
+                case UpdateBlogPathsRunner.ScanOutcome.IndexFolderMissing:
+                    // Silently exporting stale paths here would defeat the point of folding
+                    // the refresh in, so a bad root is a hard stop.
+                    Console.WriteLine($"Index folder not found at: {result.IndexPath}");
+                    Console.WriteLine("Fix the root (or pass --norefresh to export the paths already in TL.db).");
+                    return false;
+
+                default:
+                    Console.WriteLine($"Refreshed paths from {result.IndexPath}: " +
+                                      $"{result.MetadataFiles} metadata file(s), {result.Written} written, " +
+                                      $"{result.Unchanged} already correct, {result.NoLocation} without a location, " +
+                                      $"{result.NoMatchingRow} without a blog row, {result.Errors} error(s).");
+                    return true;
+            }
+        }
+
         private static void RenameExistingTxtFilesToBak(string folderPath)
         {
             try

URLNotesGrabberCORE/PostDates.cs

@@ -0,0 +1,27 @@
+using System;
+using System.Globalization;
+
+namespace URLNotesGrabberCORE
+{
+    /// <summary>
+    /// The single format for Posts.PostDate: "yyyy-MM-dd HH:mm:ss GMT", which is what the
+    /// Tumblr API sends and what nearly every row holds. Text-file exports can carry the
+    /// RFC 1123 form ("Fri, 14 Feb 2025 15:20:09 GMT") instead, which as text sorts on its
+    /// weekday name and falls outside every --fromDate/--toDate range comparison.
+    ///
+    /// Every path that writes PostDate routes through <see cref="Normalize"/>. Only the RFC 1123
+    /// form is rewritten; anything else, including the "." no-change sentinel, passes through.
+    /// </summary>
+    public static class PostDates
+    {
+        public static string? Normalize(string? value)
+        {
+            if (string.IsNullOrWhiteSpace(value)) return value;
+            string trimmed = value.Trim();
+            if (DateTime.TryParseExact(trimmed, "r", CultureInfo.InvariantCulture,
+                    DateTimeStyles.AdjustToUniversal | DateTimeStyles.AssumeUniversal, out DateTime parsed))
+                return parsed.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture) + " GMT";
+            return value;
+        }
+    }
+}

URLNotesGrabberCORE/PostTypes.cs

@@ -0,0 +1,123 @@
+using System;
+using System.Collections.Generic;
+using System.IO;
+
+namespace URLNotesGrabberCORE
+{
+    /// <summary>
+    /// The single source of truth for Posts.PostType values.
+    ///
+    /// PostType exists so --output can write one .txt per type. Because the type becomes a
+    /// *filename*, an unvalidated value is not a cosmetic problem: it creates a file. That is
+    /// how "Unknown.txt" came about -- OutputMode used `PostType ?? "Unknown"` as a filename,
+    /// --ingest then read that file straight back and derived the literal type "Unknown" from
+    /// its name, and the pair would have kept regenerating each other indefinitely.
+    ///
+    /// So every path that produces a type routes through <see cref="Normalize"/>, which admits
+    /// only the eight known names and returns null for anything else. A null type is safe:
+    /// OutputMode skips those rows rather than inventing a file for them.
+    /// </summary>
+    public static class PostTypes
+    {
+        // The canonical set. These are exactly the TumblThree .txt basenames, which is what
+        // makes an ingested filename usable as a type without translation.
+        public const string Texts = "texts";
+        public const string Answers = "answers";
+        public const string Quotes = "quotes";
+        public const string Links = "links";
+        public const string Conversations = "conversations";
+        public const string Images = "images";
+        public const string Videos = "videos";
+        public const string Audios = "audios";
+
+        private static readonly HashSet<string> Known = new HashSet<string>(
+            new[] { Texts, Answers, Quotes, Links, Conversations, Images, Videos, Audios },
+            StringComparer.OrdinalIgnoreCase);
+
+        // Tumblr's legacy post format (npf=false) names types in the singular. The likes API is
+        // the one source that reports a type directly rather than via a filename, so it is the
+        // only place this mapping is needed.
+        private static readonly Dictionary<string, string> ApiTypeMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
+        {
+            ["text"] = Texts,
+            ["photo"] = Images,
+            ["quote"] = Quotes,
+            ["link"] = Links,
+            ["chat"] = Conversations,
+            ["answer"] = Answers,
+            ["audio"] = Audios,
+            ["video"] = Videos,
+        };
+
+        /// <summary>
+        /// Returns the canonical type name, or null if the value is not one of the eight.
+        /// Returning null rather than passing the value through is the whole point: an
+        /// unrecognized string must never reach a filename.
+        /// </summary>
+        public static string? Normalize(string? candidate)
+        {
+            if (string.IsNullOrWhiteSpace(candidate)) return null;
+            string trimmed = candidate.Trim();
+            return Known.TryGetValue(trimmed, out string? canonical) ? canonical : null;
+        }
+
+        /// <summary>
+        /// Type for a post read out of a TumblThree export file, taken from the filename
+        /// ("texts.txt" -> "texts"). Anything else in the folder -- README.txt, a stray
+        /// triage file, or a previously written Unknown.txt -- normalizes to null and is
+        /// rejected by the caller.
+        /// </summary>
+        public static string? FromFileName(string? path)
+        {
+            if (string.IsNullOrWhiteSpace(path)) return null;
+            return Normalize(Path.GetFileNameWithoutExtension(path));
+        }
+
+        /// <summary>
+        /// Type for a post from the likes API, whose legacy-format `type` field is singular.
+        /// Null when the field is absent or unrecognized -- the access is dynamic, so a missing
+        /// field yields null at runtime rather than failing to compile.
+        /// </summary>
+        public static string? FromApiType(string? apiType)
+        {
+            if (string.IsNullOrWhiteSpace(apiType)) return null;
+            return ApiTypeMap.TryGetValue(apiType.Trim(), out string? mapped) ? mapped : null;
+        }
+
+        /// <summary>
+        /// Last-resort type inferred from which content columns a row actually carries. Used
+        /// only to backfill rows written before any type was recorded; a filename or an API
+        /// type is always preferred over this.
+        ///
+        /// The order matters and is derived from the already-typed rows, where the column
+        /// signatures are effectively disjoint: answers carry Question+Answer and no Body,
+        /// images carry photo columns and no Body, texts carry Body and no photo columns.
+        ///
+        /// HasImage is deliberately NOT consulted: it is set on 12,420 of 19,828 known text
+        /// posts, so it says nothing about the post's type.
+        ///
+        /// conversations cannot be separated from texts this way -- both carry only Body -- so
+        /// a chat post with no other signal is labelled texts. A later --ingest that meets the
+        /// post in a real conversations.txt corrects it.
+        /// </summary>
+        public static string? InferFromContent(string? question, string? answer, string? quote,
+            string? link, string? audioCaption, string? body, string? photoUrl, string? photoCaption)
+        {
+            if (HasValue(question) && HasValue(answer)) return Answers;
+            if (HasValue(quote)) return Quotes;
+            if (HasValue(link)) return Links;
+            if (HasValue(audioCaption)) return Audios;
+            if (HasValue(body)) return Texts;
+            if (HasValue(photoUrl) || HasValue(photoCaption)) return Images;
+            return null;
+        }
+
+        // "." is the codebase-wide "field not supplied" sentinel in export records, so it
+        // counts as absent here just as it does in UpdatePost's CASE guards.
+        private static bool HasValue(string? value)
+        {
+            if (string.IsNullOrWhiteSpace(value)) return false;
+            return value.Trim() != ".";
+        }
+    }
+}

URLNotesGrabberCORE/Program.cs

@@ -44,6 +44,8 @@ namespace URLNotesGrabberCORE
             bool apiExplicitlySet = false;
             string startFromBlogName = string.Empty;
             bool forceIgnoreCooldown = false;
+            DateTime? fromDate = null;
+            DateTime? toDate = null;
             List<string> filteredArgs = new List<string>();
             for (int i = 0; i < args.Length; i++)
             {
@@ -60,6 +62,34 @@ namespace URLNotesGrabberCORE
                     continue;
                 }
 
+                if (string.Equals(args[i], "--fromDate", StringComparison.OrdinalIgnoreCase))
+                {
+                    if (i + 1 < args.Length && DateTime.TryParse(args[i + 1], out DateTime parsedFromDate))
+                    {
+                        fromDate = parsedFromDate;
+                        i++;
+                    }
+                    else
+                    {
+                        Console.WriteLine("--Missing or unparseable date after --fromDate. Ignoring.--");
+                    }
+                    continue;
+                }
+
+                if (string.Equals(args[i], "--toDate", StringComparison.OrdinalIgnoreCase))
+                {
+                    if (i + 1 < args.Length && DateTime.TryParse(args[i + 1], out DateTime parsedToDate))
+                    {
+                        toDate = parsedToDate;
+                        i++;
+                    }
+                    else
+                    {
+                        Console.WriteLine("--Missing or unparseable date after --toDate. Ignoring.--");
+                    }
+                    continue;
+                }
+
                 if (string.Equals(args[i], "--api3", StringComparison.OrdinalIgnoreCase))
                 {
                     apiSectionName = "TumblrApi3";
@@ -148,6 +178,12 @@ namespace URLNotesGrabberCORE
             if (args.Length == 0) //Traverse folder structure to add posts and thus blogs to DB
             {
                 int postsAdded = 0;
+                // This is the mode that actually gets run day to day, so the schema migration and
+                // the PostType backfill have to happen here too. They used to hang off --ingest,
+                // --output and friends only, which meant the untyped rows this traversal creates
+                // could sit unrepaired indefinitely while the one command everyone runs skipped
+                // the fix entirely. Idempotent, so paying it on every run costs nothing.
+                DataAccess.EnsureTTFileHelperColumnsExist();
                 try
                 {
                     DataAccess.EnableImportModePragmas();
@@ -220,7 +256,7 @@ namespace URLNotesGrabberCORE
 
                         if (args.Length < 2)
                         {
-                            Console.WriteLine("--Expected WITHOUTNOTESONLY (0, 1) [OPTIONAL: BEFOREDATE]--");
+                            Console.WriteLine("--Expected WITHOUTNOTESONLY (0, 1) [OPTIONAL: BEFOREDATE] [OPTIONAL: BLOGNAME]--");
                             exitCode = 2;
                             break;
                         }
@@ -242,28 +278,63 @@ namespace URLNotesGrabberCORE
                             Console.WriteLine("Without Notes Only: {0}\t{1}", withoutNotesOnly, args[1]);
                         }
 
-                        // Parse optional beforeDate parameter
-                        if (args.Length >= 3 && !string.IsNullOrEmpty(args[2]))
+                        // Trailing arguments are the optional cutoff date and the optional blog filter, in
+                        // either order. A token that parses as a date is the cutoff; anything else is a blog
+                        // name -- which is why an unparseable token is no longer an error here. "--blog=name"
+                        // forces the blog reading for the rare name that would otherwise parse as a date.
+                        string? collectBlogName = null;
+                        bool badCollectArg = false;
+
+                        for (int i = 2; i < args.Length; i++)
                         {
-                            if (DateTime.TryParse(args[2], out DateTime parsedDate))
+                            string arg = args[i];
+                            if (string.IsNullOrWhiteSpace(arg))
+                                continue;
+
+                            if (arg.StartsWith("--blog=", StringComparison.OrdinalIgnoreCase))
+                            {
+                                collectBlogName = arg.Substring("--blog=".Length);
+                                if (string.IsNullOrWhiteSpace(collectBlogName))
+                                {
+                                    Console.WriteLine("ERROR: --blog= requires a blog name");
+                                    badCollectArg = true;
+                                    break;
+                                }
+                            }
+                            else if (!explicitDateSupplied && DateTime.TryParse(arg, out DateTime parsedDate))
                             {
                                 beforeDate = parsedDate;
                                 explicitDateSupplied = true;
                                 Console.WriteLine($"Filter: Collecting notes for posts with NotesGatheredDateTime < {beforeDate}");
                             }
+                            else if (collectBlogName == null)
+                            {
+                                collectBlogName = arg;
+                            }
                             else
                             {
-                                Console.WriteLine($"ERROR: Invalid date format '{args[2]}'");
-                                exitCode = 2;
+                                Console.WriteLine($"ERROR: Unexpected argument '{arg}'");
+                                badCollectArg = true;
                                 break;
                             }
                         }
 
+                        if (badCollectArg)
+                        {
+                            exitCode = 2;
+                            break;
+                        }
+
+                        if (collectBlogName != null)
+                            Console.WriteLine($"Filter: Collecting notes for posts by '{collectBlogName}' only");
+
                         // Mode 0 (full re-check) with no explicit date is a *managed* run: freeze the cutoff and
                         // persist it so an interrupted run resumes against the same cutoff and a completed run stops
-                        // instead of restarting. Mode 1 and explicit-date runs keep their existing behavior.
+                        // instead of restarting. Mode 1, explicit-date and blog-scoped runs keep their existing
+                        // behavior -- a single blog covers a slice of the worklist, so letting it write the shared
+                        // run state would mark the whole re-check complete after collecting one blog.
                         bool managedCollectRun = false;
-                        if (!withoutNotesOnly && !explicitDateSupplied)
+                        if (!withoutNotesOnly && !explicitDateSupplied && collectBlogName == null)
                         {
                             DataAccess.EnsureCollectRunStateTableExists();
                             var runState = DataAccess.GetCollectRunState();
@@ -281,7 +352,22 @@ namespace URLNotesGrabberCORE
                             managedCollectRun = true;
                         }
 
-                        exitCode = CollectNotes(settings.GetValue<string>("PathOutput"), withoutNotesOnly, beforeDate, managedCollectRun).GetAwaiter().GetResult();
+                        if (forceIgnoreCooldown)
+                            Console.WriteLine(withoutNotesOnly
+                                ? "--force: ignoring the periodic re-collect cooldown; already-collected posts in scope are re-queued now"
+                                : "--force: no effect in mode 0 - a full re-check already re-collects every post");
+
+                        if (fromDate.HasValue)
+                            Console.WriteLine(withoutNotesOnly
+                                ? $"--fromDate: only re-queuing already-collected posts originally posted on/after {fromDate.Value} (applies with or without --force)"
+                                : "--fromDate: no effect in mode 0 - it only bounds the periodic re-queue branch");
+
+                        if (toDate.HasValue)
+                            Console.WriteLine(withoutNotesOnly
+                                ? $"--toDate: only re-queuing already-collected posts originally posted on/before {toDate.Value} (applies with or without --force)"
+                                : "--toDate: no effect in mode 0 - it only bounds the periodic re-queue branch");
+
+                        exitCode = CollectNotes(settings.GetValue<string>("PathOutput"), withoutNotesOnly, beforeDate, managedCollectRun, collectBlogName, forceIgnoreCooldown, fromDate, toDate).GetAwaiter().GetResult();
                         break;
 
                     case "--blogsR": //collect notes from all posts
@@ -343,7 +429,7 @@ namespace URLNotesGrabberCORE
                         break;
 
                     case "--output":
-                        exitCode = OutputMode.Run(config);
+                        exitCode = OutputMode.Run(config, args.Skip(1).ToArray());
                         break;
 
                     case "--revert":
@@ -413,7 +499,7 @@ namespace URLNotesGrabberCORE
 
             Console.WriteLine("--blogs\t For each Blog in DB, write blogname to file");
 
-            Console.WriteLine("--collect [0|1] [datetime]\t Collect Notes from API. 1=only posts without notes. 0=full re-check of all posts: a single resumable pass (interrupt & relaunch to resume; stops when complete, retrigger for a new pass). Optional datetime overrides the cutoff and runs as a one-off (bypasses resume tracking).");
+            Console.WriteLine("--collect [0|1] [datetime] [blogname]\t Collect Notes from API. 1=only posts without notes. 0=full re-check of all posts: a single resumable pass (interrupt & relaunch to resume; stops when complete, retrigger for a new pass). Optional datetime overrides the cutoff and runs as a one-off (bypasses resume tracking). Optional blogname restricts the run to that blog (exact, case-sensitive match) and also runs as a one-off; e.g. \"--collect 1 zomb-eh\". datetime and blogname may be given in either order - use --blog=name if a blog name would otherwise parse as a date. Add --force to ignore the periodic re-collect cooldown and re-queue already-collected posts immediately (mode 1 only). Add --fromDate <datetime> / --toDate <datetime> to only re-queue already-collected posts originally posted on/after / on/before that date (mode 1 only; either or both may be given; applies with or without --force).");
 
             Console.WriteLine("--blogsR\t For each Note that is a REPLY, write blogname to file ");
 
@@ -425,7 +511,11 @@ namespace URLNotesGrabberCORE
 
             Console.WriteLine("--likes\t Fetch likes: initial backfill for new blogs, incremental refresh for blogs past cooldown. Optional blog name forces single-blog run.");
 
-            Console.WriteLine("--force\t (with --likes) Ignore cooldown and refresh every fully-backfilled blog");
+            Console.WriteLine("--force\t Ignore refresh cooldowns: with --likes, refresh every fully-backfilled blog; with --collect 1, re-queue already-collected posts without waiting out their cooldown");
+
+            Console.WriteLine("--fromDate <datetime>\t With --collect 1, only re-queue already-collected posts originally posted on/after <datetime>. Independent of --force - applies whether or not the cooldown is also bypassed.");
+
+            Console.WriteLine("--toDate <datetime>\t With --collect 1, only re-queue already-collected posts originally posted on/before <datetime>. Independent of --force; may be combined with --fromDate for a range.");
 
             Console.WriteLine("--urldump\t Scan all posts' text columns and extract suspected URLs to configured file");
 
@@ -439,7 +529,9 @@ namespace URLNotesGrabberCORE
 
             Console.WriteLine("--ingest [blogname]\t Ingest Tumblr .txt exports from appSettings:PathTTRoot into TL.db (all blogs, or single blog if name given)");
 
-            Console.WriteLine("--output\t Export posts from TL.db back to .txt files in each blog's TTFolderPath");
+            Console.WriteLine("--output [rootPath]\t Refresh Blogs.TTFolderPath from <root>\\Index (or appSettings:PathTTRoot), then export posts from TL.db back to .txt files in each blog's folder");
+
+            Console.WriteLine("--output --norefresh\t Export without refreshing TTFolderPath first");
 
             Console.WriteLine("--revert [blogname]\t Recursively scan the PathInput tree and restore *.bak back to *.txt (current .txt saved as next-free .bkN); optional blogname filters by path substring");
 
@@ -1010,6 +1102,15 @@ namespace URLNotesGrabberCORE
                             string reblogKey = post.reblog_key?.ToString() ?? ".";
                             string link = ".";
 
+                            // No file backs a liked post, so the filename trick used everywhere
+                            // else cannot apply here. GrabLikes requests npf=false, and in the
+                            // legacy format `type` is the discriminator that decides which content
+                            // fields a post carries -- singular there, mapped to our plural names.
+                            // liked_posts is List<dynamic>, so this is resolved at runtime and a
+                            // missing field yields null rather than a compile error; an absent or
+                            // unrecognized value leaves the type NULL instead of guessing.
+                            string? apiPostType = PostTypes.FromApiType(post.type?.ToString() as string);
+
                             // Only insert if any of the data contains strings from ContainsList
                             bool shouldInsert = false;
                             string matchedFieldName = string.Empty;
@@ -1053,7 +1154,8 @@ if (shouldInsert)
                                 DataAccess.AddPost(authorBlog, postID, reblogURL, date, postURL, slug, reblogKey,
                                                         reblogName, summary, quote, body, tags, link, photoURL,
                                                         photoCaption, downloadedFiles, audioCaption, question, answer,
-                                                        title, hasImage, true, rootBlogName: rootBlogName, rootURL: rootURL);
+                                                        title, hasImage, true, rootBlogName: rootBlogName, rootURL: rootURL,
+                                                        postType: apiPostType);
                             }
                         }
 
@@ -1292,9 +1394,19 @@ if (shouldInsert)
         // blipping on one post. Past this, skipping post-by-post would just hammer a closed door.
         const int MaxConsecutiveTransient = 10;
 
-        static async Task<int> CollectNotes(string outPath, bool withoutNotesOnly = true, DateTime? beforeDate = null, bool managedRun = false)
+        static async Task<int> CollectNotes(string outPath, bool withoutNotesOnly = true, DateTime? beforeDate = null, bool managedRun = false, string? blogName = null, bool ignoreRefreshCooldown = false, DateTime? fromDate = null, DateTime? toDate = null)
         {
-            List<Tuple<string, long, long, long>> posts = DataAccess.GetPosts(withoutNotesOnly, beforeDate);
+            List<Tuple<string, long, long, long>> posts = DataAccess.GetPosts(withoutNotesOnly, beforeDate, blogName, ignoreRefreshCooldown, fromDate, toDate);
+
+            if (posts.Count == 0 && !string.IsNullOrWhiteSpace(blogName))
+            {
+                // BlogName is matched exactly, so a typo or a case mismatch looks identical to "nothing
+                // left to collect". Say so rather than reporting a silent, instant success.
+                Console.WriteLine($"No posts to collect for blog '{blogName}'. Either it is fully collected, or the name does not match a stored blog (the match is case-sensitive).");
+                if (withoutNotesOnly && !ignoreRefreshCooldown)
+                    Console.WriteLine("Already-collected posts are re-queued only once their cooldown elapses; add --force to re-collect them now.");
+                return 0;
+            }
 
             // Posts attempted (with a definitive, non-throttle result) during *this* process. Guarantees a single
             // attempt pass: once every remaining post has been attempted, the loop stops instead of spinning on a
@@ -1390,7 +1502,7 @@ if (shouldInsert)
                         }
 
                         // Re-fetch the updated list after processing the current post
-                        posts = DataAccess.GetPosts(withoutNotesOnly, beforeDate);
+                        posts = DataAccess.GetPosts(withoutNotesOnly, beforeDate, blogName, ignoreRefreshCooldown, fromDate, toDate);
                     }
                 }
 
@@ -1463,7 +1575,15 @@ if (shouldInsert)
                         string normalizedDirectoryName = NormalizeBlogFolderName(new DirectoryInfo(path).Name);
                         bool isAtOrAfterStart = string.IsNullOrWhiteSpace(startFromBlogName) || string.Compare(normalizedDirectoryName, startFromBlogName, StringComparison.OrdinalIgnoreCase) >= 0;
 
+                        // The filename is the post type ("texts.txt" -> "texts"), so only the eight
+                        // known export files are post sources. Everything else in a blog folder is
+                        // either not a post file at all (README.txt, url lists, triage scratch) or
+                        // is our own derived output -- Unknown.txt above all, which must never be
+                        // read back in as a source or it perpetuates itself.
+                        string? filePostType = PostTypes.FromFileName(file);
+
                         if (file.EndsWith(".txt", StringComparison.OrdinalIgnoreCase)
+                            && filePostType != null
                             && (string.IsNullOrEmpty(blogName) || path.IndexOf(blogName, StringComparison.OrdinalIgnoreCase) >= 0)
                             && isAtOrAfterStart)
                         {
@@ -1496,7 +1616,7 @@ if (shouldInsert)
                                                 DataAccess.AddPost(curDir, long.Parse(reblog.postID), reblog.reblogURL, reblog.date, reblog.postURL, reblog.slug, reblog.reblogKey,
                                                                     reblog.reblogName, reblog.summary, reblog.quote, reblog.body, reblog.tags, reblog.link, reblog.photoURL,
                                                                     reblog.photoCaption, reblog.downloadedFiles, reblog.audioCaption, reblog.question, reblog.answer,
-                                                                    reblog.title, false, rootURL: reblog.rootURL);
+                                                                    reblog.title, false, rootURL: reblog.rootURL, postType: filePostType);
                                                 recordImportStopwatch.Stop();
 
                                                 postsAdded++;
@@ -1644,7 +1764,7 @@ if (shouldInsert)
                                             DataAccess.AddPost(curDir, long.Parse(reblog.postID), reblog.reblogURL, reblog.date, reblog.postURL, reblog.slug, reblog.reblogKey,
                                                                     reblog.reblogName, reblog.summary, reblog.quote, reblog.body, reblog.tags, reblog.link, reblog.photoURL,
                                                                     reblog.photoCaption, reblog.downloadedFiles, reblog.audioCaption, reblog.question, reblog.answer,
-                                                                    reblog.title, true, rootURL: reblog.rootURL);
+                                                                    reblog.title, true, rootURL: reblog.rootURL, postType: filePostType);
                                             recordImportStopwatch.Stop();
 
                                             postsAdded++;

URLNotesGrabberCORE/TL.db.md

@@ -4,12 +4,46 @@ The SQLite database behind **URLNotesGrabberCORE** and its sibling crawlers, and
 [Rolodex](https://git.basso.land/jim/Rolodex) reads.
 
 Everything below was read out of the live file, not inferred from code. Counts are as of
-**2026-07-29**; re-run the queries at the bottom to refresh them.
+**2026-08-07**; re-run the queries at the bottom to refresh them.
 
 - Journal mode: **WAL** — `TL.db-wal` and `TL.db-shm` live beside the file and are part of
   the database. Copying `TL.db` alone gives you whatever was last checkpointed, not the
   current state.
-- Page size: 4096.
+- Page size: 4096. File size: 148 MB.
+
+> ### ⚠ Breaking change, 2026-08-07: `Notes` holds integer IDs, not names
+>
+> `Notes.RootBlogName`, `Notes.NoteBlogName` and `Notes.Type` **no longer exist**. They
+> are now `RootBlogId`, `NoteBlogId` and `TypeId`, resolved through the new `BlogNames`
+> and `NoteTypes` tables. Any query naming the old columns fails outright.
+>
+> There is no compatibility view. See [porting to the integer
+> schema](#porting-to-the-integer-schema) for the old-to-new translation of every query
+> shape the applications use.
+>
+> Applied by `../normalize-notes.sql`, which took the file from 207 MB to 148 MB. An
+> earlier change the same day (`../shrink-db.sql`) took it from 267 MB to 207 MB.
+
+> ### ⚠ Breaking change, 2026-09-28: `BlogNames` is gone; `Blogs.BlogId` is the only ID authority
+>
+> The IDs in `Notes` used to live in a `BlogNames` table, with a copy in `Blogs.BlogId`.
+> Nothing kept the copy current, so by 2026-09-28 12,238 blogs first seen after the
+> migration had `Blogs.BlogId = NULL`. Every `Notes`-to-`Blogs` join on `BlogId` silently
+> skipped them and their 23,148 notes, which kept them out of `GetBlogs`.
+>
+> `../retire-blognames.sql` fixed this by giving every note participant a `Blogs` row,
+> backfilling the IDs (none renumbered), making `ix_Blogs_BlogId` unique, and **dropping
+> `BlogNames`**. There is no compatibility view: any query naming it fails with
+> `no such table: BlogNames`. Two triggers now protect the IDs.
+>
+> **Porting an app:** replace `BlogNames` with `Blogs` everywhere. The columns you used,
+> `BlogId` and `BlogName`, exist there with the same meaning. A name lookup
+> (`SELECT BlogId FROM Blogs WHERE BlogName = ?`) is a primary-key probe, and an ID
+> lookup or join (`JOIN Blogs b ON b.BlogId = n.NoteBlogId`) uses the unique
+> `ix_Blogs_BlogId`. Every ID in `Notes` resolves to exactly one `Blogs` row. `Blogs.BlogId`
+> is **no longer** a stale copy, so any code or docs that distrust it can drop that
+> caveat. Never write `BlogId` or `BlogName` on a row that has an ID, and never delete such
+> a row: the triggers reject all three. See [`Blogs`](#blogs).
 
 ---
 
@@ -17,13 +51,21 @@ Everything below was read out of the live file, not inferred from code. Counts a
 
 | Table | Rows | What it is |
 |---|--:|---|
-| `Blogs` | 144,367 | The crawl registry — one row per known blog, plus crawl-state flags |
-| `Posts` | 14,589 | Stored post content. Only 3,602 blogs actually have any |
-| `Notes` | 1,189,604 | The engagement graph: `NoteBlogName` acted on `(RootBlogName, PostID)` |
+| `Blogs` | 198,560 | The crawl registry, one row per known blog plus crawl-state flags. Also the ID authority for blogs in `Notes` |
+| `Posts` | 22,468 | Stored post content. Only 3,867 blogs actually have any |
+| `Notes` | 1,234,830 | The engagement graph: `NoteBlogId` acted on `(RootBlogId, PostID)` |
 
-The engagement graph is the interesting part. 31,888 distinct blogs appear as engagers —
-far more than the 3,602 that have stored posts — which is what makes this a social graph
-rather than a post archive.
+(`Blogs` and `Notes` counts as of 2026-09-28; the rest as of 2026-08-07.)
+
+…supported by one lookup table that exists only to keep `Notes` small:
+
+| Table | Rows | What it is |
+|---|--:|---|
+| `NoteTypes` | 5 | `TypeId` ⇄ `Type`. `like`, `reblog`, `reply`, `posted`, `post_attribution` |
+
+The engagement graph is the interesting part. 20,311 distinct blogs appear as engagers —
+far more than the 3,867 that have stored posts — which is what makes this a social graph
+rather than a post archive. Only 2,771 blogs appear as the *root* of a note.
 
 ### `Blogs`
 
@@ -42,23 +84,65 @@ CREATE TABLE "Blogs" (
     LikesLastRefreshed      INTEGER DEFAULT 0,
     LikesLastNewCount       INTEGER DEFAULT 0,
     TTFolderPath            TEXT,
+    BlogId                  INTEGER,
     PRIMARY KEY("BlogName")
 );
+
+CREATE UNIQUE INDEX ix_Blogs_BlogId ON Blogs (BlogId);
+
+CREATE TRIGGER trg_Blogs_BlogId_NoDelete        -- no DELETE of a row that has a BlogId
+CREATE TRIGGER trg_Blogs_BlogId_Immutable       -- no change to its BlogId or BlogName
 ```
 
-`BlogName` is the primary key, so it is the only indexed way in. There is no index on any
-flag or date — filtering or sorting on those scans all 144k rows, which is affordable
-here and is not on `Notes`.
+`BlogName` is the primary key, so it is the only indexed way in by name. There is no index
+on any flag or date. Filtering or sorting on those scans the whole table, which is
+affordable here and is not on `Notes`.
 
-Flag distribution: `IsActive = 1` on 144,366 of 144,367 rows, `HasBeenOutput = 1` on
-5,369, `ByLikes = 1` on 2. `IsActive` carries a second meaning as of Rolodex — see
+**`BlogId` is the ID that `Notes.RootBlogId` and `Notes.NoteBlogId` store, and `Blogs` is
+the only place it lives** (since 2026-09-28; see the banner at the top). The join to
+`Notes` is one integer hop on the unique index:
+
+```sql
+FROM Blogs B JOIN Notes N ON N.NoteBlogId = B.BlogId
+```
+
+**Every blog that appears in `Notes` has a `Blogs` row with a `BlogId`.** `AddNote`
+guarantees it through `RegisterBlog`, which runs in the note's own transaction:
+
+```sql
+INSERT OR IGNORE INTO Blogs (BlogName, DateAdded, DateModified, DateCreated)
+VALUES (@name, @now, @now, @now);
+UPDATE Blogs SET BlogId = (SELECT IFNULL(MAX(BlogId), 0) + 1 FROM Blogs)
+ WHERE BlogName = @name AND BlogId IS NULL;
+```
+
+- Unlike `AddBlog`, this does **not** skip names containing `deact`. A note by a
+  deactivated blog still needs an ID, so such blogs now get registry rows too, with the
+  usual defaults (`HasBeenOutput = 0`, `IsActive` left at its default).
+- Assigning a `BlogId` is bookkeeping, so it **does not move `DateModified`**.
+- `MAX(BlogId) + 1` is safe only because an ID can never be freed. The two triggers see
+  to that: deleting a row that has a `BlogId`, or changing its `BlogId` or `BlogName`,
+  aborts. Remove a blog with `IsActive = 0` instead. A blog renamed upstream gets a new
+  row. Rows with no `BlogId` can still be deleted or renamed freely.
+- `INSERT OR REPLACE` on `Blogs` gets around the delete trigger (SQLite does not fire
+  delete triggers for REPLACE unless `recursive_triggers` is on), and it would wipe the
+  `BlogId`. It was already forbidden because it resets `IsActive`. Do not use it.
+
+**`BlogId` is NULL on 165,887 of 198,560 rows**, every blog that has never appeared in a
+note. That is the large majority, and it is not an error: the registry is far bigger than
+the engagement graph. An inner join on `BlogId` therefore silently drops those blogs,
+which is usually what you want for engagement queries and is wrong for registry listings.
+
+Flag distribution: `IsActive = 1` on 188,601 of 188,620 rows, `HasBeenOutput = 1` on
+5,059, `ByLikes = 1` on 2. `IsActive` carries a second meaning as of Rolodex — see
 [`Blogs.IsActive`](#blogsisactive--now-written-by-two-applications) below.
 
 The columns after `DateCreated` were added later by `ALTER TABLE`, which is why they carry
-no quoting in the stored DDL. That is the normal way this schema grows.
+no quoting in the stored DDL. That is the normal way this schema grows, and `BlogId` is
+the newest example.
 
-**`DateAdded` is not written consistently.** 126,423 rows hold ISO `yyyy-MM-dd HH:mm:ss`;
-17,944 hold US-format `M/d/yy` from a bulk import. As text those two sort into different
+**`DateAdded` is not written consistently.** 170,677 rows hold ISO `yyyy-MM-dd HH:mm:ss`;
+17,943 hold US-format `M/d/yy` from a bulk import. As text those two sort into different
 parts of the table, so anything ordering or range-filtering on this column has to
 normalise first — see `DateSql` in Rolodex.
 
@@ -100,73 +184,331 @@ CREATE TABLE "Posts" (
 );
 ```
 
-**The key is `(BlogName, PostID)`, not `PostID`.** This matters more than it looks: 325
+**The key is `(BlogName, PostID)`, not `PostID`.** This matters more than it looks: 345
 post IDs exist under more than one blog, so an ID on its own is both ambiguous *and*
 unindexed. Any lookup should carry the blog name, and a batch lookup should group by blog
 so it stays on the leading column of the key.
 
 Notable:
 
-- **`PostType` is `NULL` on all 14,589 rows.** The column exists but nothing has ever
-  populated it. Treat it as unpopulated rather than as a type discriminator.
-- `HasImage = 1` on 14,268 rows — nearly all of them. It records that the post *had* a
-  picture, not that a usable URL was kept, so it is not a reliable predictor that anything
-  will render.
+- **`PostType` is now mostly populated: 20,679 of 22,468 rows, leaving 1,789 `NULL`.**
+  This reverses what earlier revisions of this document said — the column really was empty
+  on every row, and something has since started writing it. Anything that treated it as
+  permanently unset, or derived the type from post content instead, should be re-examined
+  against the live data. Rolodex still derives it.
+- **`PostDate` is `yyyy-MM-dd HH:mm:ss GMT`** — UTC, as the Tumblr API sends it, unlike
+  the local-time `DateCreated`/`DateModified`. Text-file exports may carry RFC 1123
+  (`Fri, 14 Feb 2025 15:20:09 GMT`); every write path runs `PostDates.Normalize` to
+  convert it, and `../normalize-postdate.sql` fixed the 4 rows written before that.
+- `HasImage = 1` on 14,026 rows. It records that the post *had* a picture, not that a
+  usable URL was kept, so it is not a reliable predictor that anything will render.
 - `PhotoURL` is largely unused; in practice the image markup lives inside `Body`.
-- `NotFound = 1` on 4,663 rows — posts that have since been deleted upstream.
+- `NotFound = 1` on 4,712 rows — posts that have since been deleted upstream.
 - The content columns (`Body`, `Quote`, `Question`, `Answer`, …) are the heavy ones. List
   views should not select them.
 
 ### `Notes`
 
 ```sql
-CREATE TABLE "Notes" (
-    "RootBlogName"    TEXT,
-    "PostID"          INTEGER,
-    "NoteBlogName"    TEXT,
-    "TimeStamp"       INTEGER,
-    "Type"            TEXT,
-    "replyText"       TEXT DEFAULT '.',
-    "DatetimeCrawled" TEXT DEFAULT '2/12/26 12am',
-    "DateModified"    TEXT,
-    "DateCreated"     TEXT,
-    PRIMARY KEY("RootBlogName","PostID","TimeStamp","Type","NoteBlogName")
-);
+CREATE TABLE Notes (
+    RootBlogId      INTEGER NOT NULL,
+    PostID          INTEGER NOT NULL,
+    NoteBlogId      INTEGER NOT NULL,
+    TimeStamp       INTEGER NOT NULL,
+    TypeId          INTEGER NOT NULL,
+    replyText       TEXT,
+    DatetimeCrawled TEXT,
+    DateModified    TEXT,
+    DateCreated     TEXT,
+    IsActive        INTEGER NOT NULL DEFAULT 1,
+    PRIMARY KEY (RootBlogId, PostID, TimeStamp, TypeId, NoteBlogId)
+) WITHOUT ROWID;
 
-CREATE INDEX "Notes_idx_06e01ae3" ON "Notes" ("TimeStamp" DESC);
-CREATE INDEX "ix_NoteBlogName01"  ON "Notes" ("NoteBlogName");
+CREATE INDEX ix_Notes_NoteBlogId ON Notes (NoteBlogId);
 ```
 
+**Integer IDs since 2026-08-07 — this is the breaking change.** `RootBlogName`,
+`NoteBlogName` and `Type` are gone, replaced by `RootBlogId`, `NoteBlogId` and `TypeId`.
+Resolve blog IDs through `Blogs.BlogId` and types through [`NoteTypes`](#notetypes). The old names were text repeated on 1.18 million rows,
+in the table *and* in every index over it; the swap took the file from 207 MB to 148 MB.
+
+The **primary key column order is deliberately unchanged**, so the leading-prefix access
+patterns callers already depend on still hold: `(RootBlogId)` and `(RootBlogId, PostID)`
+remain cheap prefixes, exactly as `(RootBlogName)` and `(RootBlogName, PostID)` were.
+
+Two nulls-and-defaults differences from the old DDL, both intentional:
+
+- `replyText` and `DatetimeCrawled` **no longer carry column defaults**. The old table
+  defaulted them to `'.'` and `'2/12/26 12am'`, which is how 1.1M rows acquired
+  placeholder values nobody wrote. New rows now get `NULL` unless a writer supplies
+  something. The crawler names both columns explicitly, so its behaviour is unchanged.
+- The five key columns are now `NOT NULL`. They always were in practice.
+
+**`WITHOUT ROWID`, since earlier the same day.** The rows live in the primary key's
+b-tree rather than in a rowid table with a separate key index beside it. Two consequences
+matter before adding an index here:
+
+- There is no `rowid` on this table. `SELECT rowid FROM Notes` is an error, and no
+  code in any of the three apps relied on it.
+- A secondary index carries the whole five-column primary key as its row reference
+  instead of a compact rowid, so indexes here are **expensive** — though far less so
+  than before, now that the key is five integers rather than three integers and two
+  strings. `ix_Notes_NoteBlogId` costs 27 MB; its text predecessor cost 58 MB.
+
+**`Notes_idx_06e01ae3` on `TimeStamp DESC` was dropped at the same time.** It cost
+14 MB as a rowid index and would have cost 58 MB after the conversion. It was worth
+neither: the crawler's only `TimeStamp` filter (`>= 1535778000`) excludes 786 rows
+of 1.18M, Rolodex's default Notes sort carries a three-column tiebreaker that forces
+a full sort regardless, and the reply-matching `UPDATE` uses `ABS(TimeStamp - ?) <= 5`,
+which no index on `TimeStamp` can serve. The one path that got slower is Rolodex's
+Notes page with a date-range filter: 60 ms to 164 ms.
+
+See `../shrink-db.sql` for the full rationale and the applied result.
+
+**`DatetimeCrawled` is `NULL` on 1,148,077 rows, and that is the honest value.** Those
+rows previously stored the literal string `'2/12/26 12am'` — this column's own DDL
+default, written as a bulk backfill placeholder rather than as a crawl time. They were
+set to `NULL` on 2026-08-07, which is what consumers already displayed them as: the
+string parses as a date in neither format this schema writes.
+
+Note the trap: **the `DEFAULT '2/12/26 12am'` clause is still in the DDL above.** Any
+`INSERT` that omits this column writes the placeholder straight back. The crawler names
+it explicitly on every insert, so nothing reintroduces it today, but a new writer that
+forgets to would — which is why consumers should keep treating an unparseable value here
+as "unknown" rather than assuming `NULL` is now the only such marker.
+
 One row per engagement event. `TimeStamp` is **unix seconds** — unlike every date column
 elsewhere in the schema, which are text.
 
-| `Type` | Rows | Share |
-|---|--:|--:|
-| `like` | 947,955 | 79.7% |
-| `reblog` | 224,323 | 18.9% |
-| `reply` | 15,201 | 1.3% |
-| `posted` | 2,106 | 0.2% |
-| `post_attribution` | 19 | — |
-
-At 1.19M rows this is the table that dictates how the whole database has to be queried:
+At 1.18M rows this is the table that dictates how the whole database has to be queried:
 
 - **Nothing should run an unbounded `SELECT` or a bare `COUNT(*)` here.** A count scans
   the lot on every call.
-- The only fast access paths are the primary key's leading columns (`RootBlogName`, then
-  `PostID`) and `ix_NoteBlogName01` on `NoteBlogName`. "Notes received by a blog" and
+- The only fast access paths are the primary key's leading columns (`RootBlogId`, then
+  `PostID`) and `ix_Notes_NoteBlogId` on `NoteBlogId`. "Notes received by a blog" and
   "notes given by a blog" are both cheap; almost nothing else is.
-- Ordering by anything but `TimeStamp` is a full sort of whatever the filters leave.
-- `replyText` is `'.'` on 1,174,706 rows — only `reply` notes carry real text.
+- **Every** ordering here is a full sort of whatever the filters leave, `TimeStamp`
+  included. Filter first, then sort.
+- `replyText` is `'.'` on 1,167,464 rows — only `reply` notes carry real text. Those
+  dots are inherited from the old column default; new rows get `NULL` instead.
+
+**Resolve IDs by filtering `Blogs`, not by scanning `Notes`.** A name predicate on `Blogs`
+is a primary-key probe, so pushing it there costs nothing and lets the `Notes` index do
+the work:
+
+```sql
+-- good: Blogs resolves the name, then the index is searched
+SELECT * FROM Notes
+ WHERE NoteBlogId = (SELECT BlogId FROM Blogs WHERE BlogName = ?);
+
+-- also good, same plan
+SELECT n.* FROM Notes n
+  JOIN Blogs b ON b.BlogId = n.NoteBlogId
+ WHERE b.BlogName = ?;
+```
+
+### `NoteTypes`
+
+```sql
+CREATE TABLE NoteTypes (
+    TypeId INTEGER PRIMARY KEY,
+    Type   TEXT NOT NULL UNIQUE
+);
+```
+
+| `TypeId` | `Type` | Rows | Share |
+|--:|---|--:|--:|
+| 1 | `like` | 945,167 | 79.9% |
+| 2 | `reblog` | 219,203 | 18.5% |
+| 3 | `reply` | 15,345 | 1.3% |
+| 4 | `posted` | 2,617 | 0.2% |
+| 5 | `post_attribution` | 1 | — |
+
+The set is fixed in practice, but it is a table rather than a `CHECK` constraint so that
+adding a type is an `INSERT` and not a schema migration. **The IDs above are stored in
+`Notes` and must not be reassigned.**
+
+Five rows means the lookup is effectively free; write `t.Type = 'reblog'` and let SQLite
+resolve it, or hardcode the ID if you prefer — both are fine, but hardcoding ties your
+code to this table's contents, so prefer the join in anything long-lived.
+
+---
+
+## Porting to the integer schema
+
+> Written for the 2026-08-07 change, and updated for 2026-09-28: wherever this section
+> once said `BlogNames`, it now says `Blogs`. `BlogNames` no longer exists.
+
+Everything here was checked against the live 148 MB file. There were 14 affected call
+sites in `DataAccess.cs` and 16 in `RolodexRepository.cs`. TumblThree needs no changes —
+its single statement touches `Blogs.IsActive` and `BlogName` only.
+
+**`DataAccess.cs` is ported.** All 14 sites now read the integer schema, `AddNote`
+registers names and types before inserting, and `verify-db-schema.sql` reports a
+pre-migration file rather than letting the app fail on it. `RolodexRepository.cs` lives in
+the [Rolodex](https://git.basso.land/jim/Rolodex) repository and is not covered by that
+work. One site was dropped rather than translated: the `LEFT JOIN Notes` in `GetPosts`
+selected nothing and was collapsed by the query's own `GROUP BY`, so it could not affect
+the result.
+
+### Column mapping
+
+| Was | Is now | Resolve via |
+|---|---|---|
+| `Notes.RootBlogName` | `Notes.RootBlogId` | `Blogs.BlogId` → `.BlogName` |
+| `Notes.NoteBlogName` | `Notes.NoteBlogId` | `Blogs.BlogId` → `.BlogName` |
+| `Notes.Type` | `Notes.TypeId` | `NoteTypes.TypeId` → `.Type` |
+| `ix_NoteBlogName01` | `ix_Notes_NoteBlogId` | — |
+
+`PostID`, `TimeStamp`, `replyText`, `DatetimeCrawled`, `DateModified`, `DateCreated` and
+`IsActive` are unchanged.
+
+### Filtering by a blog name
+
+```sql
+-- was
+WHERE NoteBlogName = @Name
+
+-- now, either form; both search ix_Notes_NoteBlogId after a primary-key lookup
+WHERE NoteBlogId = (SELECT BlogId FROM Blogs WHERE BlogName = @Name)
+-- or
+JOIN Blogs b ON b.BlogId = n.NoteBlogId WHERE b.BlogName = @Name
+```
+
+Measured at 73 ms against 63 ms for the old text form on the busiest blog, when the lookup
+was still `BlogNames`. The extra hop is one index probe and does not show.
+
+### Joining `Notes` to `Blogs`
+
+This is the join to get right; it is the most common shape in both applications.
+
+```sql
+-- was
+FROM Blogs B INNER JOIN Notes N ON N.NoteBlogName = B.BlogName
+
+-- now: one integer hop, using Blogs.BlogId
+FROM Blogs B INNER JOIN Notes N ON N.NoteBlogId = B.BlogId
+```
+
+Joining `Notes` to `Posts` also goes through `Blogs`, since `Posts` has only a name:
+
+```sql
+FROM Posts P
+JOIN Blogs RB ON RB.BlogName = P.BlogName
+JOIN Notes N  ON N.RootBlogId = RB.BlogId AND N.PostID = P.PostID
+```
+
+### Selecting a name back out
+
+```sql
+-- was
+SELECT NoteBlogName AS blogName, COUNT(*) FROM Notes ... GROUP BY NoteBlogName
+
+-- now
+SELECT b.BlogName AS blogName, COUNT(*)
+  FROM Notes n JOIN Blogs b ON b.BlogId = n.NoteBlogId
+ ... GROUP BY b.BlogName
+```
+
+Group by `n.NoteBlogId` instead of `b.BlogName` when you only need the name for display —
+grouping on the integer is cheaper and the name comes along for free.
+
+### Filtering by type
+
+```sql
+-- was
+WHERE type IN ('reblog', 'reply', 'posted')
+
+-- now
+WHERE TypeId IN (SELECT TypeId FROM NoteTypes WHERE Type IN ('reblog','reply','posted'))
+-- or, equivalently
+JOIN NoteTypes t ON t.TypeId = n.TypeId WHERE t.Type IN ('reblog','reply','posted')
+```
+
+`WHERE TypeId IN (2,3,4)` also works and is marginally faster, but hardcodes this table's
+contents into application code. Prefer the lookup outside of hot paths.
+
+Note the negated form needs care: `type NOT IN ('reblog','reply','posted')` becomes
+`TypeId NOT IN (SELECT TypeId FROM NoteTypes WHERE Type IN (...))`, which is correct only
+because `TypeId` is `NOT NULL`.
+
+### Inserting a note
+
+The crawler must ensure both blogs have IDs first: run the `RegisterBlog` pair shown under
+[`Blogs`](#blogs) for each name. No read-back, no round trip, and safe to run every time.
+Then:
+
+```sql
+INSERT OR IGNORE INTO Notes
+       (RootBlogId, PostID, NoteBlogId, TimeStamp, TypeId,
+        DatetimeCrawled, DateModified, DateCreated)
+SELECT (SELECT BlogId FROM Blogs WHERE BlogName = @rootBlogName),
+       @PostID,
+       (SELECT BlogId FROM Blogs WHERE BlogName = @noteBlogName),
+       @TimeStamp,
+       (SELECT TypeId FROM NoteTypes WHERE Type = @Type),
+       @DatetimeCrawled, @DateModified, @DateCreated;
+```
+
+Verified: a genuinely new note inserts, and re-running the identical statement inserts 0.
+Run the registrations and the insert in one transaction so a crash cannot leave a blog
+registered with no note.
+
+**The duplicate-key error message has changed.** `DataAccess.cs` compares against the
+literal string
+
+```
+UNIQUE constraint failed: Notes.RootBlogName, Notes.PostID, Notes.TimeStamp, Notes.Type, Notes.NoteBlogName
+```
+
+at two call sites to decide whether to swallow an exception. SQLite now emits the *new*
+column names, so those comparisons no longer match and real errors will surface where
+they used to be silently ignored — or vice versa.
+
+Both sites now go through `IsNotesDuplicateKey` in `DataAccess.cs`, which matches on
+`UNIQUE constraint failed` plus `Notes.` rather than on the column list. A literal
+comparison is what broke here; the next rename should not break it again.
+
+### Updating notes
+
+Predicates translate the same way. The reply-matching update, which cannot use an index
+on `TimeStamp` either before or after:
+
+```sql
+-- now
+UPDATE Notes SET replyText = @replyText, DateModified = @dateModified
+ WHERE NoteBlogId = (SELECT BlogId FROM Blogs WHERE BlogName = @noteBlogName)
+   AND ABS(TimeStamp - @TimeStamp) <= 5
+   AND TypeId = (SELECT TypeId FROM NoteTypes WHERE Type = 'reply')
+   AND (replyText IS NULL OR replyText = '' OR replyText = '.')
+   AND (replyText IS NULL OR replyText <> @replyText);
+```
+
+Rolodex's soft-delete updates need no change beyond the `WHERE` clause — they set
+`IsActive`, which is untouched.
+
+### Two traps
+
+**`Blogs.BlogId` is NULL on 165,887 of 198,560 rows.** Any inner join on it silently drops
+every blog that has never appeared in a note. Correct for engagement queries; wrong for
+registry listings, which need a `LEFT JOIN` or no join at all.
+
+**IDs are stable and must stay so.** `Blogs.BlogId` and `NoteTypes.TypeId` are stored in
+over a million `Notes` rows. Never renumber. A blog renamed upstream gets a new row, not
+an edited one. The `Blogs` triggers reject both.
+
+---
 
 ### Referential integrity
 
 There are no foreign keys, and the tables do not perfectly agree:
 
-- 4 `Posts` rows name a blog with no `Blogs` row.
-- 15 of the 31,888 distinct engagers have no `Blogs` row.
-
-So a name appearing in `Notes` or `Posts` is not a guarantee that the registry knows about
-it. Joins from those tables back to `Blogs` should tolerate a miss.
+- 4 `Posts` rows name a blog with no `Blogs` row, so joins from `Posts` back to `Blogs`
+  should tolerate a miss.
+- `Notes` is covered: every `RootBlogId` and `NoteBlogId` resolves to a `Blogs` row.
+  `retire-blognames.sql` checked this before committing, and `RegisterBlog` keeps it true.
+  Before 2026-09-28, 12 to 17 note participants had no registry row. They now have stub
+  rows.
 
 ---
 
@@ -178,10 +520,14 @@ consumer.
 
 | Column | `'.'` rows |
 |---|--:|
-| `Notes.replyText` | 1,174,706 |
-| `Posts.Title` | 13,144 |
+| `Notes.replyText` | 1,167,464 |
+| `Posts.Title` | 12,562 |
 | `Posts.Body` | 172 |
 
+`Notes.replyText` and `Notes.DatetimeCrawled` **no longer carry column defaults** as of
+the integer migration, so new note rows get `NULL` rather than a placeholder. The dots
+already in `replyText` were not rewritten — cleaning is still required on read.
+
 Any query whose output reaches a human should collapse it:
 
 ```sql
@@ -214,9 +560,11 @@ Crawler bookkeeping. Rolodex ignores all of these.
 `DataAccess.cs` joins on it to decide what to collect:
 
 ```sql
-SELECT NoteBlogName, count(*) FROM notes
-INNER JOIN blogs ON blogs.BlogName = notes.NoteBlogName
-WHERE blogs.IsActive = @isActive AND ...
+-- shape only
+SELECT b.BlogName, count(*)
+  FROM Notes n
+  JOIN Blogs b ON b.BlogId = n.NoteBlogId
+ WHERE b.IsActive = @isActive AND ...
 ```
 
 Nothing inside the crawler *writes* it — it is an input, set from outside.
@@ -262,12 +610,16 @@ under its Posts and Notes pages. Removing a blog hides the blog, not what it col
 
 ---
 
-## `Posts.IsActive` and `Notes.IsActive` — optional, and not in this database yet
+## `Posts.IsActive` and `Notes.IsActive` — present, and written from outside
 
-The same flag is being extended to the two content tables, with the same meaning: `0` is
-removed, anything else — including `NULL` — is live. **Neither column exists in the live
-`TL.db` as of 2026-07-29**; the DDL quoted above for `Posts` and `Notes` is complete. Like
-`Blogs.IsActive`, they are written from outside this crawler.
+The same flag extends to the two content tables, with the same meaning: `0` is removed,
+anything else — including `NULL` — is live. **Both columns now exist in the live `TL.db`**
+and are included in the DDL quoted above. As of 2026-08-07, `Posts.IsActive = 0` on 5,900
+rows and `Notes.IsActive = 0` on none. Like `Blogs.IsActive`, they are written from
+outside this crawler.
+
+On `Notes` the column is `INTEGER NOT NULL DEFAULT 1`, so a `NULL` cannot occur there;
+`Posts` and `Blogs` are laxer, which is why the predicate below still uses `COALESCE`.
 
 The crawler therefore treats both as optional, and as nothing it owns:
 
@@ -308,10 +660,18 @@ handled:
 ```sql
 SELECT 'Blogs', COUNT(*) FROM Blogs
 UNION ALL SELECT 'Posts',     COUNT(*) FROM Posts
-UNION ALL SELECT 'Notes', COUNT(*) FROM Notes;
+UNION ALL SELECT 'Notes',     COUNT(*) FROM Notes
+UNION ALL SELECT 'Blogs with a BlogId', COUNT(*) FROM Blogs WHERE BlogId IS NOT NULL;
 
--- note type mix
-SELECT Type, COUNT(*) FROM Notes GROUP BY Type ORDER BY 2 DESC;
+-- note type mix (joins NoteTypes; Notes.Type no longer exists)
+SELECT t.Type, COUNT(*)
+  FROM Notes n JOIN NoteTypes t ON t.TypeId = n.TypeId
+ GROUP BY t.Type ORDER BY 2 DESC;
+
+-- how much of the registry participates in the engagement graph
+SELECT COUNT(*) FILTER (WHERE BlogId IS NOT NULL) AS with_notes,
+       COUNT(*) FILTER (WHERE BlogId IS NULL)     AS without_notes
+  FROM Blogs;
 
 -- the two date shapes in Blogs.DateAdded
 SELECT CASE WHEN DateAdded LIKE '____-__-__%' THEN 'ISO' ELSE 'US' END, COUNT(*)
@@ -324,6 +684,14 @@ SELECT COUNT(*) FROM (
 -- rows that reference a blog the registry does not have
 SELECT COUNT(*) FROM Posts p
 WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogName = p.BlogName);
+
+-- note participants with no Blogs.BlogId (expect 0; anything else is the pre-2026-09-28 drift)
+SELECT COUNT(*) FROM (SELECT DISTINCT NoteBlogId AS Id FROM Notes) n
+WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogId = n.Id);
+
+-- space by object, to see where the file actually goes
+SELECT name, SUM(pgsize)/1024/1024 AS mb
+  FROM dbstat GROUP BY name ORDER BY SUM(pgsize) DESC;
 ```
 
 Open the file read-only so an inspection can never disturb a running crawl:

URLNotesGrabberCORE/UpdateBlogPathsRunner.cs

@@ -4,34 +4,64 @@ namespace URLNotesGrabberCORE
 {
     // Port of ThreeTxtFileHelper/UpdateBlogPaths.cs. Reads .tumblr / .tmblrpriv metadata
     // files from a root\Index folder and populates Blogs.TTFolderPath in TL.db.
+    //
+    // Scan() is the reusable engine: --updatepaths wraps it as a standalone command and
+    // --output calls it as a refresh step, because a TL.db synced between machines cannot
+    // hold one absolute path that is correct on both.
     public static class UpdateBlogPathsRunner
     {
-        public static int Run(string rootPath)
+        public enum ScanOutcome
+        {
+            Completed,
+            NoRootConfigured,
+            IndexFolderMissing
+        }
+
+        public sealed class ScanResult
+        {
+            public ScanOutcome Outcome { get; init; }
+            public string RootPath { get; init; } = string.Empty;
+            public string IndexPath { get; init; } = string.Empty;
+            public int MetadataFiles { get; init; }
+            public int Written { get; init; }
+            public int Unchanged { get; init; }
+            public int NoLocation { get; init; }
+            public int NoMatchingRow { get; init; }
+            public int Errors { get; init; }
+        }
+
+        // verbose: log a line per metadata file. --updatepaths wants that detail; --output
+        // only wants the counts, since a few hundred lines before the export would bury it.
+        public static ScanResult Scan(string? rootPath, bool verbose)
         {
             if (string.IsNullOrWhiteSpace(rootPath))
-            {
-                Console.WriteLine("UpdateBlogPaths: rootPath is required.");
-                return 1;
-            }
+                return new ScanResult { Outcome = ScanOutcome.NoRootConfigured };
 
             DataAccess.EnsureTTFileHelperColumnsExist();
 
             string indexPath = Path.Combine(rootPath, "Index");
             if (!Directory.Exists(indexPath))
             {
-                Console.WriteLine($"Index folder not found at: {indexPath}");
-                return 1;
+                return new ScanResult
+                {
+                    Outcome = ScanOutcome.IndexFolderMissing,
+                    RootPath = rootPath,
+                    IndexPath = indexPath
+                };
             }
 
-            Console.WriteLine($"Scanning Index folder: {indexPath}");
-
             var blogFiles = Directory.GetFiles(indexPath, "*.tumblr")
                 .Concat(Directory.GetFiles(indexPath, "*.tmblrpriv"))
                 .ToList();
 
+            if (verbose)
                 Console.WriteLine($"Found {blogFiles.Count} blog metadata files");
 
             int updatedCount = 0;
+            int unchangedCount = 0;
+            int noLocationCount = 0;
+            int noRowCount = 0;
+            int errorCount = 0;
 
             foreach (var blogFile in blogFiles)
             {
@@ -44,27 +74,91 @@ namespace URLNotesGrabberCORE
 
                     if (root.TryGetProperty("FileDownloadLocation", out JsonElement locationElement))
                     {
-                        string? fileDownloadLocation = locationElement.GetString();
+                        string? fileDownloadLocation = locationElement.GetString()?.Trim();
                         if (!string.IsNullOrWhiteSpace(fileDownloadLocation))
                         {
-                            DataAccess.SetBlogTTFolderPath(blogName, fileDownloadLocation);
+                            // Report the database's answer, not the fact that the file parsed.
+                            if (DataAccess.SetBlogTTFolderPath(blogName, fileDownloadLocation))
+                            {
                                 updatedCount++;
+                                if (verbose)
                                     Console.WriteLine($"Updated {blogName}: {fileDownloadLocation}");
                             }
+                            else if (DataAccess.BlogExists(blogName))
+                            {
+                                unchangedCount++;
+                            }
+                            else
+                            {
+                                noRowCount++;
+                                Console.WriteLine($"No Blogs row named '{blogName}' -- path not stored (name may differ in case)");
+                            }
                         }
                         else
                         {
+                            noLocationCount++;
+                            if (verbose)
+                                Console.WriteLine($"Empty FileDownloadLocation in {blogFile}");
+                        }
+                    }
+                    else
+                    {
+                        noLocationCount++;
+                        if (verbose)
                             Console.WriteLine($"No FileDownloadLocation found in {blogFile}");
                     }
                 }
                 catch (Exception ex)
                 {
+                    errorCount++;
                     Console.WriteLine($"Error processing {blogFile}: {ex.Message}");
                 }
             }
 
-            Console.WriteLine($"\nUpdated {updatedCount} blogs with TTFolderPath");
-            return 0;
+            return new ScanResult
+            {
+                Outcome = ScanOutcome.Completed,
+                RootPath = rootPath,
+                IndexPath = indexPath,
+                MetadataFiles = blogFiles.Count,
+                Written = updatedCount,
+                Unchanged = unchangedCount,
+                NoLocation = noLocationCount,
+                NoMatchingRow = noRowCount,
+                Errors = errorCount
+            };
+        }
+
+        public static int Run(string rootPath)
+        {
+            if (string.IsNullOrWhiteSpace(rootPath))
+            {
+                Console.WriteLine("UpdateBlogPaths: rootPath is required.");
+                return 1;
+            }
+
+            string indexPath = Path.Combine(rootPath, "Index");
+            Console.WriteLine($"Scanning Index folder: {indexPath}");
+
+            var result = Scan(rootPath, verbose: true);
+
+            if (result.Outcome == ScanOutcome.IndexFolderMissing)
+            {
+                Console.WriteLine($"Index folder not found at: {result.IndexPath}");
+                return 1;
+            }
+
+            Console.WriteLine($"\n========== UpdateBlogPaths summary ==========");
+            Console.WriteLine($"Metadata files:           {result.MetadataFiles}");
+            Console.WriteLine($"TTFolderPath written:     {result.Written}");
+            Console.WriteLine($"Already correct:          {result.Unchanged}");
+            Console.WriteLine($"No FileDownloadLocation:  {result.NoLocation}");
+            Console.WriteLine($"No matching blog row:     {result.NoMatchingRow}");
+            Console.WriteLine($"Errors:                   {result.Errors}");
+
+            Console.WriteLine($"\nBlogs now holding a TTFolderPath: {DataAccess.CountBlogsWithTTFolderPath()}");
+
+            return result.Errors == 0 ? 0 : 2;
         }
     }
 }

normalize-notes.sql

@@ -0,0 +1,147 @@
+-- normalize-notes.sql
+-- Replaces the repeated blog-name and type TEXT in Notes with integer IDs.
+-- Reduces TL.db from ~207 MB to ~148 MB (-29%).
+--
+-- SUPERSEDED IN PART, 2026-09-28: the BlogNames table this creates is no longer the
+-- ID authority. Run retire-blognames.sql straight after this one; it moves the IDs
+-- into Blogs.BlogId and drops BlogNames. The current app code assumes both have run.
+--
+-- THIS IS A BREAKING SCHEMA CHANGE. There is no compatibility layer. Every
+-- query in URLNotesGrabberCORE and Rolodex that names Notes.RootBlogName,
+-- Notes.NoteBlogName or Notes.Type stops working the moment this runs, and
+-- stays broken until those queries are rewritten. This was a deliberate choice
+-- over a view-plus-triggers shim, which was measured to work but cost 194 ms ->
+-- 321 ms on Rolodex's unfiltered Notes page.
+--
+-- TumblThree is unaffected. It touches only Blogs, and the column added to
+-- Blogs here is additive.
+--
+-- HOW TO RUN (DB Browser for SQLite):
+--   1. Stop all three apps. Pause NextCloud sync.
+--   2. Back up TL.db.
+--   3. Execute SQL, paste this file, run. Then Write Changes.
+--   4. Tools > Compact Database (VACUUM). Nothing shrinks until this finishes.
+
+--------------------------------------------------------------------------
+-- The shape this produces
+--------------------------------------------------------------------------
+--   BlogNames(BlogId, BlogName)   the ID authority: every name appearing in
+--                                 Notes as either participant. 20,430 rows.
+--                                 12 of these have no Blogs row -- the
+--                                 registry has never been a superset of the
+--                                 engagement graph, and still is not.
+--
+--   NoteTypes(TypeId, Type)       5 rows. Fixed set, but written as a table
+--                                 rather than a CHECK so a new type is an
+--                                 INSERT and not a schema migration.
+--
+--   Notes(...Id columns...)       integer FKs in place of text. WITHOUT ROWID,
+--                                 same 5-column key in the same column order.
+--
+--   Blogs.BlogId                  NEW additive column. Lets Notes join Blogs in
+--                                 one integer hop instead of going through
+--                                 BlogNames and comparing text at the end.
+--                                 NULL on the 168,202 blogs with no notes.
+
+PRAGMA foreign_keys = off;
+
+BEGIN;
+
+--------------------------------------------------------------------------
+-- STEP 1: the ID authority
+--------------------------------------------------------------------------
+CREATE TABLE BlogNames (
+    BlogId   INTEGER PRIMARY KEY,
+    BlogName TEXT NOT NULL UNIQUE
+);
+
+INSERT INTO BlogNames (BlogName)
+    SELECT RootBlogName FROM Notes
+    UNION
+    SELECT NoteBlogName FROM Notes;
+
+--------------------------------------------------------------------------
+-- STEP 2: the type lookup
+--------------------------------------------------------------------------
+CREATE TABLE NoteTypes (
+    TypeId INTEGER PRIMARY KEY,
+    Type   TEXT NOT NULL UNIQUE
+);
+
+-- IDs are assigned explicitly and must stay stable: they are stored in Notes.
+INSERT INTO NoteTypes (TypeId, Type) VALUES
+    (1, 'like'),
+    (2, 'reblog'),
+    (3, 'reply'),
+    (4, 'posted'),
+    (5, 'post_attribution');
+
+--------------------------------------------------------------------------
+-- STEP 3: rebuild Notes with integer keys
+--------------------------------------------------------------------------
+-- Column order of the primary key is unchanged from the text version, so the
+-- leading-column access patterns callers already rely on still hold:
+-- (RootBlogId) and (RootBlogId, PostID) remain cheap prefixes.
+CREATE TABLE NotesN (
+    RootBlogId      INTEGER NOT NULL,
+    PostID          INTEGER NOT NULL,
+    NoteBlogId      INTEGER NOT NULL,
+    TimeStamp       INTEGER NOT NULL,
+    TypeId          INTEGER NOT NULL,
+    replyText       TEXT,
+    DatetimeCrawled TEXT,
+    DateModified    TEXT,
+    DateCreated     TEXT,
+    IsActive        INTEGER NOT NULL DEFAULT 1,
+    PRIMARY KEY (RootBlogId, PostID, TimeStamp, TypeId, NoteBlogId)
+) WITHOUT ROWID;
+
+-- Inner joins are safe here: BlogNames was just built from these very columns,
+-- and NoteTypes covers all 5 values present. A row that failed to match would
+-- be silently dropped, which is what the row-count check at the bottom is for.
+INSERT INTO NotesN
+SELECT r.BlogId, n.PostID, b.BlogId, n.TimeStamp, t.TypeId,
+       n.replyText, n.DatetimeCrawled, n.DateModified, n.DateCreated, n.IsActive
+  FROM Notes n
+  JOIN BlogNames r ON r.BlogName = n.RootBlogName
+  JOIN BlogNames b ON b.BlogName = n.NoteBlogName
+  JOIN NoteTypes t ON t.Type     = n.Type;
+
+DROP TABLE Notes;
+ALTER TABLE NotesN RENAME TO Notes;
+
+-- Replaces ix_NoteBlogName01. Renamed because it indexes a different column now.
+CREATE INDEX ix_Notes_NoteBlogId ON Notes (NoteBlogId);
+
+--------------------------------------------------------------------------
+-- STEP 4: give Blogs the matching id
+--------------------------------------------------------------------------
+-- Additive: no existing column changes, so TumblThree's
+-- "UPDATE Blogs SET IsActive = 0 ... WHERE BlogName = ?" is untouched.
+ALTER TABLE Blogs ADD COLUMN BlogId INTEGER;
+
+UPDATE Blogs
+   SET BlogId = (SELECT bn.BlogId FROM BlogNames bn WHERE bn.BlogName = Blogs.BlogName);
+
+CREATE INDEX ix_Blogs_BlogId ON Blogs (BlogId);
+
+COMMIT;
+
+--------------------------------------------------------------------------
+-- STEP 5: Write Changes, then Tools > Compact Database
+--------------------------------------------------------------------------
+-- From the CLI instead:  sqlite3 TL.db "VACUUM;"
+
+--------------------------------------------------------------------------
+-- VERIFY
+--------------------------------------------------------------------------
+-- PRAGMA integrity_check;                         -- expect: ok
+-- SELECT COUNT(*) FROM Notes;                     -- expect: 1182333, unchanged
+-- SELECT COUNT(*) FROM BlogNames;                 -- expect: 20430
+-- SELECT COUNT(*) FROM Blogs WHERE BlogId IS NOT NULL;   -- expect: 20418
+--
+-- Losslessness was proven before this ran, by reconstructing the old text shape
+-- from the new schema and diffing it against the original both ways:
+--   SELECT COUNT(*) FROM (SELECT * FROM old.Notes EXCEPT SELECT * FROM Rebuilt);
+--   SELECT COUNT(*) FROM (SELECT * FROM Rebuilt EXCEPT SELECT * FROM old.Notes);
+-- Both returned 0 across all 1,182,333 rows and all 10 columns.

normalize-postdate.sql

@@ -0,0 +1,31 @@
+-- normalize-postdate.sql
+-- Rewrites Posts.PostDate values held in RFC 1123 form ("Fri, 14 Feb 2025 15:20:09 GMT")
+-- into the column's canonical "yyyy-MM-dd HH:mm:ss GMT" (the Tumblr API's own format).
+--
+-- As of 2026-09-21 this matched 4 rows, all zombaee, from one text-file import. As text
+-- they sort on the weekday name and never satisfy --fromDate / --toDate comparisons.
+-- New writes are normalized in code by PostDates.Normalize, so this is a one-off.
+--
+-- Only PostDate changes. DateModified is left alone: the post content did not change.
+--
+-- HOW TO RUN: back up TL.db, then from the URLNotesGrabberCORE project folder:
+--     sqlite3 TL.db < ../normalize-postdate.sql
+
+SELECT 'before', COUNT(*) FROM Posts WHERE PostDate LIKE '___, __ ___ ____ __:__:__ GMT';
+
+BEGIN;
+UPDATE Posts
+   SET PostDate = substr(PostDate, 13, 4) || '-' ||
+                  printf('%02d', (instr('JanFebMarAprMayJunJulAugSepOctNovDec', substr(PostDate, 9, 3)) + 2) / 3) || '-' ||
+                  substr(PostDate, 6, 2) || ' ' ||
+                  substr(PostDate, 18)
+ WHERE PostDate LIKE '___, __ ___ ____ __:__:__ GMT'
+   AND instr('JanFebMarAprMayJunJulAugSepOctNovDec', substr(PostDate, 9, 3)) % 3 = 1;
+COMMIT;
+
+-- VERIFY: expect 0, then a single shape '9999-99-99 99:99:99 GMT' (plus any NULL/blank)
+SELECT 'after', COUNT(*) FROM Posts WHERE PostDate LIKE '___, %';
+SELECT CASE WHEN PostDate GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9] GMT'
+            THEN 'yyyy-MM-dd HH:mm:ss GMT' ELSE IFNULL(PostDate, '(null)') END AS shape,
+       COUNT(*)
+  FROM Posts GROUP BY 1 ORDER BY 2 DESC;

retire-blognames.sql

@@ -0,0 +1,143 @@
+-- retire-blognames.sql
+-- Makes Blogs.BlogId the only ID authority for Notes and retires the BlogNames table.
+--
+-- WHY: normalize-notes.sql (2026-08-07) put the IDs in BlogNames and copied them into
+-- Blogs.BlogId once. Nothing kept the copy current: by 2026-09-28, 12,238 blogs first seen
+-- in a note after the migration had a BlogNames ID but Blogs.BlogId = NULL, so every query
+-- joining Notes to Blogs on BlogId (GetBlogs and friends) silently skipped them -- 23,148
+-- notes. Two copies of one ID drift; this leaves one.
+--
+-- What it does:
+--   1. Gives every BlogNames name a Blogs row (17 had none), carrying its ID over.
+--   2. Copies the ID onto every Blogs row that is missing it. IDs are never renumbered --
+--      they are stored in 1.18M Notes rows.
+--   3. Proves every Notes ID resolves through Blogs before anything is dropped.
+--   4. Makes ix_Blogs_BlogId UNIQUE.
+--   5. Drops BlogNames. No compatibility view: any other app that still names it gets
+--      "no such table: BlogNames" and must port to Blogs.BlogId (see TL.db.md).
+--   6. Adds triggers that stop a Blogs row holding a BlogId from being deleted, renamed or
+--      renumbered -- the guarantees BlogNames gave by never being touched.
+--
+-- DateModified is NOT moved: assigning an ID is bookkeeping, not a content change. The 17
+-- new stub rows get DateAdded/DateModified/DateCreated = now, as AddBlog would give them.
+--
+-- Runs after normalize-notes.sql. A backup from before 2026-08-07 needs both, in order.
+--
+-- HOW TO RUN:
+--   1. Stop every app that uses TL.db. Pause NextCloud sync.
+--   2. Back up TL.db:   sqlite3 TL.db ".backup 'TL pre-retire-blognames.db'"
+--   3. sqlite3 -bail TL.db < retire-blognames.sql
+--      -bail matters: a failed check aborts before COMMIT and nothing is changed.
+--      In DB Browser, Execute SQL stops at the first error; then Revert Changes.
+--   4. Run the build of URLNotesGrabberCORE that no longer uses BlogNames. An older
+--      build fails every AddNote with "no such table: BlogNames".
+
+PRAGMA foreign_keys = off;
+
+BEGIN;
+
+-- Every check inserts one count here; the CHECK aborts the script on anything but 0.
+CREATE TEMP TABLE MustBeZero (Check_ TEXT, n INTEGER CHECK (n = 0));
+
+--------------------------------------------------------------------------
+-- STEP 0: the two copies must not disagree anywhere they are both set
+--------------------------------------------------------------------------
+INSERT INTO MustBeZero
+SELECT 'Blogs.BlogId differs from BlogNames', COUNT(*)
+  FROM Blogs b JOIN BlogNames bn ON bn.BlogName = b.BlogName
+ WHERE b.BlogId <> bn.BlogId;
+
+INSERT INTO MustBeZero
+SELECT 'Blogs.BlogId unknown to BlogNames', COUNT(*)
+  FROM Blogs b
+ WHERE b.BlogId IS NOT NULL
+   AND NOT EXISTS (SELECT 1 FROM BlogNames bn WHERE bn.BlogId = b.BlogId AND bn.BlogName = b.BlogName);
+
+--------------------------------------------------------------------------
+-- STEP 1: a Blogs row for every name Notes points at
+--------------------------------------------------------------------------
+-- No IsActive in the column list: it is not ours to write (defaults to live).
+INSERT INTO Blogs (BlogName, DateAdded, DateModified, DateCreated, BlogId)
+SELECT bn.BlogName,
+       strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'),
+       strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'),
+       strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'),
+       bn.BlogId
+  FROM BlogNames bn
+ WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogName = bn.BlogName);
+
+--------------------------------------------------------------------------
+-- STEP 2: backfill the IDs Blogs never received
+--------------------------------------------------------------------------
+UPDATE Blogs
+   SET BlogId = (SELECT bn.BlogId FROM BlogNames bn WHERE bn.BlogName = Blogs.BlogName)
+ WHERE BlogId IS NULL
+   AND BlogName IN (SELECT BlogName FROM BlogNames);
+
+--------------------------------------------------------------------------
+-- STEP 3: prove Blogs now holds exactly what BlogNames held
+--------------------------------------------------------------------------
+INSERT INTO MustBeZero
+SELECT 'BlogNames pair missing from Blogs', COUNT(*)
+  FROM BlogNames bn
+ WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogId = bn.BlogId AND b.BlogName = bn.BlogName);
+
+INSERT INTO MustBeZero
+SELECT 'Blogs IDs vs BlogNames rows', (SELECT COUNT(*) FROM Blogs WHERE BlogId IS NOT NULL) - (SELECT COUNT(*) FROM BlogNames);
+
+INSERT INTO MustBeZero
+SELECT 'Notes.RootBlogId unresolved', COUNT(*)
+  FROM (SELECT DISTINCT RootBlogId AS Id FROM Notes) n
+ WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogId = n.Id);
+
+INSERT INTO MustBeZero
+SELECT 'Notes.NoteBlogId unresolved', COUNT(*)
+  FROM (SELECT DISTINCT NoteBlogId AS Id FROM Notes) n
+ WHERE NOT EXISTS (SELECT 1 FROM Blogs b WHERE b.BlogId = n.Id);
+
+--------------------------------------------------------------------------
+-- STEP 4: one row per ID
+--------------------------------------------------------------------------
+-- UNIQUE still allows the NULLs on the ~168k blogs that have never appeared in a note.
+DROP INDEX ix_Blogs_BlogId;
+CREATE UNIQUE INDEX ix_Blogs_BlogId ON Blogs (BlogId);
+
+--------------------------------------------------------------------------
+-- STEP 5: BlogNames goes
+--------------------------------------------------------------------------
+DROP TABLE BlogNames;
+
+--------------------------------------------------------------------------
+-- STEP 6: what BlogNames guaranteed by never being written
+--------------------------------------------------------------------------
+-- A deleted row would orphan its notes, and MAX(BlogId) + 1 in RegisterBlog could then
+-- hand the same ID to a different blog. Remove a blog with IsActive = 0 instead.
+CREATE TRIGGER trg_Blogs_BlogId_NoDelete
+BEFORE DELETE ON Blogs
+WHEN OLD.BlogId IS NOT NULL
+BEGIN
+    SELECT RAISE(ABORT, 'Blogs row has a BlogId that Notes points at; set IsActive = 0 instead of deleting');
+END;
+
+-- A blog renamed upstream is a new blog to Tumblr's API and gets a new row. Editing the name
+-- in place would re-attribute every note to it; changing the ID would orphan them.
+CREATE TRIGGER trg_Blogs_BlogId_Immutable
+BEFORE UPDATE OF BlogId, BlogName ON Blogs
+WHEN OLD.BlogId IS NOT NULL
+ AND (NEW.BlogId IS NOT OLD.BlogId OR NEW.BlogName IS NOT OLD.BlogName)
+BEGIN
+    SELECT RAISE(ABORT, 'BlogId and BlogName are fixed once a blog has a BlogId; Notes rows point at it');
+END;
+
+DROP TABLE temp.MustBeZero;
+
+COMMIT;
+
+--------------------------------------------------------------------------
+-- VERIFY
+--------------------------------------------------------------------------
+-- SELECT COUNT(*) FROM sqlite_master WHERE name = 'BlogNames';            -- expect: 0
+-- SELECT COUNT(*) FROM Blogs WHERE BlogId IS NOT NULL;                     -- expect: the old BlogNames row count
+-- SELECT sql FROM sqlite_master WHERE name = 'ix_Blogs_BlogId';            -- expect: CREATE UNIQUE INDEX
+-- SELECT name FROM sqlite_master WHERE type = 'trigger';                   -- expect: both triggers
+-- PRAGMA integrity_check;                                                  -- expect: ok

shrink-db.sql

@@ -0,0 +1,159 @@
+-- shrink-db.sql
+-- Reduces TL.db from ~267 MB to ~207 MB (-22%) with no application changes,
+-- and no visible change in any of the three apps that touch this file.
+--
+-- The three consumers, and what each one uses:
+--   URLNotesGrabberCORE  System.Data.SQLite 1.0.119  writes Notes, Posts, Blogs
+--   Rolodex (web)        Microsoft.Data.Sqlite 10.0  reads all three; soft-deletes via IsActive
+--   TumblThree           System.Data.SQLite.Core 1.0.119
+--                        one statement only, ManagerController.cs:972 --
+--                        "UPDATE Blogs SET IsActive = 0, DateModified = @DateModified
+--                         WHERE BlogName = @BlogName"
+--                        Nothing below touches the Blogs table, so TumblThree is
+--                        unaffected. (Its GlobalDatabaseService talks to TumblThree's
+--                        own separate FileEntries/BlogFiles database, not this file.)
+--
+-- WITHOUT ROWID needs SQLite >= 3.8.2 (Dec 2013). All three providers above are
+-- 2024-25 builds, an order of magnitude newer, so STEP 2 is readable by all of them.
+--
+-- Every figure below was measured on a copy of the live 267 MB file, and the
+-- result was checked against all three apps' access patterns:
+--   PRAGMA integrity_check ....... ok
+--   row counts ................... Notes 1182333, Posts 22468, Blogs 188620 (unchanged)
+--   Rolodex soft-delete UPDATE ... works
+--   Rolodex NoteBlogName filter .. still uses ix_NoteBlogName01
+--   crawler INSERT OR IGNORE ..... still dedupes (0 dupes admitted)
+--   TumblThree's UPDATE Blogs ..... untouched -- Blogs is not modified by this script
+--
+-- HOW TO RUN (DB Browser for SQLite):
+--   1. Stop ALL THREE apps: the crawler, the Rolodex web app, and TumblThree.
+--      Rolodex holds the file open and checkpoints the WAL, so it must be down,
+--      not just idle. TumblThree only opens the file for an instant when you
+--      delete a blog, but it can also launch the crawler on its own
+--      (UrlNotesGrabberService) -- so close it rather than merely avoiding it.
+--   2. Back up TL.db (copy the 267 MB file somewhere safe).
+--   3. Open TL.db, go to Execute SQL, paste STEP 1-3, run.
+--   4. Click "Write Changes".
+--   5. Run Tools > Compact Database. This is VACUUM; it will not run from the
+--      Execute SQL tab because DB Browser keeps a transaction open there.
+--      NOTHING SHRINKS ON DISK UNTIL THIS FINISHES.
+--
+-- Expected: steps 1-3 a couple of minutes, Compact a couple more.
+-- Free disk needed during Compact: ~270 MB for the temp copy.
+
+--------------------------------------------------------------------------
+-- STEP 1: drop the TimeStamp index          (required by STEP 2, not optional)
+--------------------------------------------------------------------------
+-- Measured cost/benefit:
+--
+--   * Rolodex's DEFAULT Notes view does not use it. Its sort carries the
+--     tiebreaker "RootBlogName, PostID, NoteBlogName", which forces a full sort
+--     regardless -- the query plan is byte-identical with and without the index.
+--     Sorting.cs:138 already assumes as much, and is right in practice.
+--   * The crawler's collect query (DataAccess.cs:1183) filters
+--     TimeStamp >= 1535778000, which excludes 786 of 1,182,333 rows (0.07%).
+--     A full index scan wearing a disguise. Same measured time without it.
+--   * The reply-matching UPDATE uses ABS(TimeStamp - ?) <= 5, which can never
+--     use an index on TimeStamp.
+--   * It DOES help exactly one path: Rolodex's Notes page with a date-range
+--     filter applied. 60 ms -> 164 ms. That is the whole of what is lost.
+--
+-- And it must go, because after STEP 2 it stops being cheap. A secondary index
+-- on a WITHOUT ROWID table carries the full 5-column primary key instead of a
+-- compact rowid, so this index grows 14 MB -> 58 MB. Keeping it lands the file
+-- at 265 MB instead of 207 MB -- i.e. it cancels the entire exercise to save
+-- 100 ms on one filtered view.
+
+DROP INDEX IF EXISTS Notes_idx_06e01ae3;
+
+--------------------------------------------------------------------------
+-- STEP 2: rebuild Notes as WITHOUT ROWID                       (-32 MB)
+--------------------------------------------------------------------------
+-- Notes has a 5-column composite primary key. In a rowid table SQLite stores
+-- that key twice: once in the table, once in sqlite_autoindex_Notes_1 (62 MB).
+-- WITHOUT ROWID stores the rows *in* the key's b-tree, so the copy disappears.
+--
+-- ix_NoteBlogName01 grows 25 -> 58 MB for the reason described above. Net -32 MB.
+-- It is kept because Rolodex filters on NoteBlogName and the crawler joins on it.
+--
+-- Safe: neither codebase references rowid on Notes (grep across both trees,
+-- zero matches). IsActive keeps its exact current declaration, which is what
+-- Rolodex's ActiveFlag predicate reads.
+
+PRAGMA foreign_keys = off;
+
+CREATE TABLE Notes_new (
+    "RootBlogName"      TEXT,
+    "PostID"            INTEGER,
+    "NoteBlogName"      TEXT,
+    "TimeStamp"         INTEGER,
+    "Type"              TEXT,
+    "replyText"         TEXT DEFAULT '.',
+    "DatetimeCrawled"   TEXT DEFAULT '2/12/26 12am',
+    "DateModified"      TEXT,
+    "DateCreated"       TEXT,
+    IsActive            INTEGER NOT NULL DEFAULT 1,
+    PRIMARY KEY("RootBlogName","PostID","TimeStamp","Type","NoteBlogName")
+) WITHOUT ROWID;
+
+INSERT INTO Notes_new
+SELECT RootBlogName, PostID, NoteBlogName, TimeStamp, Type,
+       replyText, DatetimeCrawled, DateModified, DateCreated, IsActive
+  FROM Notes;
+
+DROP TABLE Notes;
+ALTER TABLE Notes_new RENAME TO Notes;
+
+CREATE INDEX "ix_NoteBlogName01" ON "Notes" ("NoteBlogName");
+
+--------------------------------------------------------------------------
+-- STEP 3: clear the DatetimeCrawled placeholder                (-13 MB)
+--------------------------------------------------------------------------
+-- 1,148,077 of 1,182,333 rows hold the literal DDL default '2/12/26 12am' --
+-- a backfill placeholder, not a crawl time. SQLite stores all 12 bytes of it
+-- on every one of those rows.
+--
+-- This is UI-NEUTRAL in Rolodex, which is why it is safe despite Rolodex
+-- displaying the column. Rolodex reads and sorts it through DateSql.Sortable
+-- (DateRange.cs:67), whose CASE matches '____-__-__%' or the 8-character
+-- '__/__/__'. The 12-character '2/12/26 12am' matches neither, so Sortable
+-- already returns NULL for these rows and the page already renders an em dash
+-- and sorts them to the bottom. RolodexRepository.cs:957-963 documents exactly
+-- this. Writing a real NULL changes the bytes on disk, not the screen.
+--
+-- The crawler never reads the column back -- it only writes it on INSERT
+-- (DataAccess.cs:713, 721).
+
+UPDATE Notes SET DatetimeCrawled = NULL WHERE DatetimeCrawled = '2/12/26 12am';
+
+--------------------------------------------------------------------------
+-- DELIBERATELY NOT DONE: nulling Notes.DateCreated
+--------------------------------------------------------------------------
+-- An earlier draft of this script also cleared DateCreated = '2026-04-13'
+-- (a further -12 MB). Do not. Unlike DatetimeCrawled, that value DOES match
+-- Sortable's '____-__-__%' branch, so Rolodex renders it as a real date in the
+-- "Created" column on the Notes page and Post detail, and sorts by it. Nulling
+-- it would turn visible dates into em dashes and move rows in the sort order.
+
+--------------------------------------------------------------------------
+-- STEP 4: Write Changes, then Tools > Compact Database
+--------------------------------------------------------------------------
+-- Nothing above reclaims disk until VACUUM runs. From the sqlite3 CLI instead:
+--     sqlite3 TL.db "VACUUM;"
+
+--------------------------------------------------------------------------
+-- VERIFY (run after compacting; file should be ~207 MB)
+--------------------------------------------------------------------------
+-- PRAGMA integrity_check;
+--
+-- SELECT 'Notes' t, COUNT(*) n FROM Notes
+-- UNION ALL SELECT 'Posts', COUNT(*) FROM Posts
+-- UNION ALL SELECT 'Blogs', COUNT(*) FROM Blogs;
+--   -- expect 1182333 / 22468 / 188620, unchanged
+--
+-- SELECT name, SUM(pgsize)/1024/1024 AS mb
+--   FROM dbstat GROUP BY name ORDER BY SUM(pgsize) DESC;
+--   -- expect Notes 78, ix_NoteBlogName01 58, Posts 50, Blogs 14
+--
+-- APPLIED 2026-08-07. Actual result: 267.32 MB -> 207.17 MB, integrity_check ok,
+-- row counts unchanged, journal_mode still wal. VACUUM took 6 seconds.

verify-db-schema.sql

@@ -79,18 +79,36 @@ WITH expected(tbl, col, alter_stmt) AS (
     ('Blogs','LikesLastRefreshed',    'ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;'),
     ('Blogs','LikesLastNewCount',     'ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;'),
     ('Blogs','TTFolderPath',          'ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;'),
+    -- Blogs.BlogId (2026-08-07) is the single-hop join key into Notes. Deliberately NOT
+    -- auto-fixable: an added-but-empty BlogId makes every engagement join return zero
+    -- rows silently, which is worse than the hard error a missing column gives.
+    -- Since 2026-09-28 it is the only blog-ID authority (query 1e).
+    ('Blogs','BlogId',                'MANUAL REVIEW - see queries 1d/1e: run normalize-notes.sql, then retire-blognames.sql'),
 
     -- Notes (base columns: manual review if missing)
-    ('Notes','RootBlogName',          'MANUAL REVIEW - base/PK column missing'),
+    -- Integer IDs since 2026-08-07. RootBlogName/NoteBlogName/Type are GONE, not renamed
+    -- in place -- a backup that still has them needs normalize-notes.sql, not an ALTER.
+    -- Query 1d below reports exactly that case.
+    ('Notes','RootBlogId',            'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
     ('Notes','PostID',                'MANUAL REVIEW - base/PK column missing'),
-    ('Notes','NoteBlogName',          'MANUAL REVIEW - base/PK column missing'),
+    ('Notes','NoteBlogId',            'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
     ('Notes','TimeStamp',             'MANUAL REVIEW - base/PK column missing'),
-    ('Notes','Type',                  'MANUAL REVIEW - base/PK column missing'),
+    ('Notes','TypeId',                'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
     ('Notes','DatetimeCrawled',       'MANUAL REVIEW - base column missing'),
     ('Notes','DateModified',          'MANUAL REVIEW - base column missing'),
     ('Notes','DateCreated',           'MANUAL REVIEW - base column missing'),
     -- Notes (additive migration column, auto-fixable)
-    ('Notes','replyText',             'ALTER TABLE Notes ADD COLUMN replyText TEXT DEFAULT ''.'';'),
+    -- No DEFAULT: the migrated schema dropped it, so new rows get NULL rather than a
+    -- placeholder. EnsureReplyTextColumnExists in DataAccess.cs adds it the same way.
+    ('Notes','replyText',             'ALTER TABLE Notes ADD COLUMN replyText TEXT;'),
+
+    -- NoteTypes (the lookup table Notes resolves TypeId through, 2026-08-07).
+    -- Not auto-fixable: an empty NoteTypes does not mean "add the table", it means the
+    -- Notes rows have nothing to resolve against. Rebuild with normalize-notes.sql.
+    -- BlogNames is not listed: it was dropped on 2026-09-28. Query 1e reports a file
+    -- that still has it.
+    ('NoteTypes','TypeId',            'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
+    ('NoteTypes','Type',              'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
 
     -- DailyAPICount (base columns)
     ('DailyAPICount','Date',          'MANUAL REVIEW - base/PK column missing'),
@@ -106,6 +124,7 @@ actual(tbl, col) AS (
   SELECT 'Posts',           name FROM pragma_table_info('Posts')
   UNION ALL SELECT 'Blogs',           name FROM pragma_table_info('Blogs')
   UNION ALL SELECT 'Notes',           name FROM pragma_table_info('Notes')
+  UNION ALL SELECT 'NoteTypes',       name FROM pragma_table_info('NoteTypes')
   UNION ALL SELECT 'DailyAPICount',   name FROM pragma_table_info('DailyAPICount')
   UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
   UNION ALL SELECT 'ApiKeyPoolMeta',  name FROM pragma_table_info('ApiKeyPoolMeta')
@@ -127,7 +146,7 @@ ORDER BY (e.alter_stmt LIKE 'ALTER%') DESC, e.tbl, e.col;
 -- 1b. MISSING TABLES: expected tables that don't exist at all in this DB.
 --     Zero rows = good.
 WITH expected_tables(tbl) AS (
-  VALUES ('Posts'),('Blogs'),('Notes'),('DailyAPICount'),
+  VALUES ('Posts'),('Blogs'),('Notes'),('NoteTypes'),('DailyAPICount'),
          ('ApiKeyPoolState'),('ApiKeyPoolMeta')
 )
 SELECT et.tbl AS missing_table
@@ -157,10 +176,11 @@ WITH expected(tbl, col) AS (
     ('Blogs','BlogName'),('Blogs','HasBeenOutput'),('Blogs','IsActive'),('Blogs','DateAdded'),
     ('Blogs','ByLikes'),('Blogs','DateModified'),('Blogs','DateCreated'),('Blogs','LikesPulled'),
     ('Blogs','LikesCursor'),('Blogs','LikesNewestTimestamp'),('Blogs','LikesLastRefreshed'),
-    ('Blogs','LikesLastNewCount'),('Blogs','TTFolderPath'),
-    ('Notes','RootBlogName'),('Notes','PostID'),('Notes','NoteBlogName'),('Notes','TimeStamp'),
-    ('Notes','Type'),('Notes','DatetimeCrawled'),('Notes','DateModified'),('Notes','DateCreated'),
+    ('Blogs','LikesLastNewCount'),('Blogs','TTFolderPath'),('Blogs','BlogId'),
+    ('Notes','RootBlogId'),('Notes','PostID'),('Notes','NoteBlogId'),('Notes','TimeStamp'),
+    ('Notes','TypeId'),('Notes','DatetimeCrawled'),('Notes','DateModified'),('Notes','DateCreated'),
     ('Notes','replyText'),('Notes','IsActive'),
+    ('NoteTypes','TypeId'),('NoteTypes','Type'),
     ('DailyAPICount','Date'),('DailyAPICount','APICount'),
     ('ApiKeyPoolState','KeyName'),('ApiKeyPoolState','RetryUntil'),
     ('ApiKeyPoolMeta','Id'),('ApiKeyPoolMeta','LastIndex')
@@ -169,6 +189,7 @@ actual(tbl, col) AS (
   SELECT 'Posts',           name FROM pragma_table_info('Posts')
   UNION ALL SELECT 'Blogs',           name FROM pragma_table_info('Blogs')
   UNION ALL SELECT 'Notes',           name FROM pragma_table_info('Notes')
+  UNION ALL SELECT 'NoteTypes',       name FROM pragma_table_info('NoteTypes')
   UNION ALL SELECT 'DailyAPICount',   name FROM pragma_table_info('DailyAPICount')
   UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
   UNION ALL SELECT 'ApiKeyPoolMeta',  name FROM pragma_table_info('ApiKeyPoolMeta')
@@ -181,6 +202,47 @@ WHERE e.col IS NULL
 ORDER BY a.tbl, a.col;
 
 
+-- 1d. PRE-MIGRATION DATABASE: a backup from before 2026-08-07, when Notes still
+--     stored names. Zero rows = good.
+--
+--     This is the one failure SECTION 2 cannot fix. Notes.RootBlogName /
+--     NoteBlogName / Type were replaced by RootBlogId / NoteBlogId / TypeId
+--     resolving through (then) BlogNames and NoteTypes -- a data migration, not an
+--     ADD COLUMN. There is no compatibility view, so the current code fails
+--     outright ("no such column: RootBlogId") against such a file.
+--
+--     Fix: run normalize-notes.sql against a COPY of the backup, then re-run
+--     SECTION 1. Do not hand-add the ID columns: they would be empty, and an
+--     empty NoteBlogId is indistinguishable from a note by blog #0.
+SELECT 'Notes still stores names -- run normalize-notes.sql on a copy' AS pre_migration_schema,
+       group_concat(name, ', ')                                        AS legacy_columns_found
+FROM pragma_table_info('Notes')
+WHERE lower(name) IN ('rootblogname','noteblogname','type')
+HAVING COUNT(*) > 0;
+
+
+-- 1e. BLOGNAMES NOT RETIRED: a backup from between 2026-08-07 and 2026-09-28, when
+--     BlogNames still held the IDs and Blogs.BlogId was an
+--     unmaintained copy. Zero rows = good.
+--
+--     The current code resolves every Notes ID through Blogs.BlogId and never writes
+--     BlogNames, so against such a file new blogs get IDs that can collide with
+--     BlogNames' and every blog missing from Blogs.BlogId stays invisible to GetBlogs.
+--
+--     Fix: back up, then run retire-blognames.sql (after normalize-notes.sql if 1d
+--     also reported). It checks itself and changes nothing if a check fails.
+SELECT 'BlogNames still exists (' || type || ') -- run retire-blognames.sql' AS blognames_not_retired
+FROM sqlite_master
+WHERE lower(name) = 'blognames'
+UNION ALL
+SELECT 'Blogs.BlogId is not UNIQUE -- run retire-blognames.sql'
+WHERE NOT EXISTS (SELECT 1 FROM pragma_index_list('Blogs') WHERE name = 'ix_Blogs_BlogId' AND "unique" = 1)
+UNION ALL
+SELECT 'BlogId guard trigger missing: ' || t.name || ' -- run retire-blognames.sql'
+FROM (SELECT 'trg_Blogs_BlogId_NoDelete' AS name UNION ALL SELECT 'trg_Blogs_BlogId_Immutable') t
+WHERE NOT EXISTS (SELECT 1 FROM sqlite_master m WHERE m.type = 'trigger' AND m.name = t.name);
+
+
 -- ============================================================================
 -- SECTION 2  --  FIX (opt-in, additive only)
 --
@@ -190,6 +252,9 @@ ORDER BY a.tbl, a.col;
 -- "duplicate column name" error and changes nothing -- just run the flagged
 -- subset. These are the 8 additive migration columns and nothing else; the
 -- likes high-water-mark reset is intentionally NOT included.
+--
+-- Nothing here addresses queries 1d or 1e. Those are data migrations
+-- (normalize-notes.sql, retire-blognames.sql) and cannot be reached by adding columns.
 -- ============================================================================
 
 -- ALTER TABLE Posts ADD COLUMN PostType TEXT;
@@ -199,4 +264,4 @@ ORDER BY a.tbl, a.col;
 -- ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;
 -- ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;
 -- ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;
--- ALTER TABLE Notes ADD COLUMN replyText TEXT DEFAULT '.';
+-- ALTER TABLE Notes ADD COLUMN replyText TEXT;