Merge branch 'claude/sqlite-modified-date-logic-3d9bee' into master

ReblogRecord (TraverseDirectory's .txt-export parser) and the --likes API
path both default every content field to the literal "." when their source
has no value for that field, then pass it straight into UpdatePost. A blog
with two export folders in different field formats -- a duplicate "_2"
folder, or an export whose field set changed over time -- sends one record
with real Title/Tags/Slug and another with those fields "." because that
format never had a line for them. Re-importing both on every run flipped
the row back and forth forever: net content never changed, but
DateModified moved on every pass since each write really did change a
column relative to the other write, just not relative to the true value.

Every content column in UpdatePost's SET list is now guarded the same way
RootBlogName/RootURL already were -- a "." parameter leaves the existing
value alone instead of overwriting it -- and the change-detection WHERE
clause carries the same exception, so a "."-only difference no longer
fires the UPDATE at all. Deliberately narrow: only the literal "." is the
sentinel, so an explicit empty string from a real record still overwrites.

Verified against a throwaway DB using the exact SQL text and parameter
binding from UpdatePost, reproducing the an-angry-wolf/adore-blk scenario
found in the live DB: re-importing conflicting "." records now writes zero
rows and leaves DateModified untouched, while a genuine content change
still fires and still moves it.

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

AGENTS.md

@@ -67,6 +67,63 @@ 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
+
 ### Testing
 - No existing test suite; use xUnit if adding tests
 - Test critical logic: `ApiKeyPool` init, color parsing, config persistence

URLNotesGrabberCORE/DataAccess.cs

@@ -598,7 +598,7 @@ namespace URLNotesGrabberCORE
                         {
                             if (ownsConnection) connection.Open();
 
-                            string updateSql = "UPDATE Posts SET hasImage = @hasImage, DateModified = @DateModified WHERE blogName = @blogName AND postID = @postID";
+                            string updateSql = "UPDATE Posts SET hasImage = @hasImage, DateModified = @DateModified WHERE blogName = @blogName AND postID = @postID AND IFNULL(hasImage, 0) <> @hasImage";
                             using SQLiteCommand updateCommand = new SQLiteCommand(updateSql, connection);
                             updateCommand.Parameters.AddWithValue("@hasImage", hasImage ? 1 : 0);
                             updateCommand.Parameters.AddWithValue("@DateModified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
@@ -623,16 +623,17 @@ namespace URLNotesGrabberCORE
                     }
                 }
 
-                // Only update HasBeenOutput and DateAdded if a new post was inserted
+                // Only reopen the blog for output if a new post was inserted. DateAdded records
+                // when the blog first entered the registry and is never rewritten here -- a new
+                // post is not a new blog.
                 if (rowsInserted == 1)
                 {
                     try
                     {
-                        string updateBlogSql = "UPDATE Blogs SET HasBeenOutput = 0, DateAdded = @DateAdded, DateModified = @DateModified WHERE BlogName = @BlogName";
+                        string updateBlogSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName AND (HasBeenOutput IS NULL OR HasBeenOutput <> 0)";
                         using (var updateBlogCommand = new SQLiteCommand(updateBlogSql, connection))
                         {
                             updateBlogCommand.Parameters.AddWithValue("@BlogName", blogName);
-                            updateBlogCommand.Parameters.AddWithValue("@DateAdded", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                             updateBlogCommand.Parameters.AddWithValue("@DateModified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                             updateBlogCommand.ExecuteNonQuery();
                         }
@@ -737,7 +738,9 @@ namespace URLNotesGrabberCORE
                     {
                         try
                         {
-                            string updateSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName";
+                            // HasBeenOutput IS NULL still counts as a change: the selection queries
+                            // test HasBeenOutput = 0, which a NULL would never match.
+                            string updateSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName AND (HasBeenOutput IS NULL OR HasBeenOutput <> 0)";
                             using (var updateCommand = new SQLiteCommand(updateSql, connection2))
                             {
                                 updateCommand.Parameters.AddWithValue("@BlogName", noteBlogName);
@@ -1356,7 +1359,10 @@ namespace URLNotesGrabberCORE
                 connection.Open();
 
                 //string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered WHERE BlogName = @BlogName AND PostID = @PostID";
-                string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered, DateModified = @dateModified WHERE PostID = @PostID AND (IFNULL(HasNotesGathered, 0) <> 1 OR IFNULL(NotesGatheredDateTime, 0) <> @notesGathered)";
+                // NotesGatheredDateTime is crawl bookkeeping -- it moves on every pass and says
+                // nothing about the post itself, so only the HasNotesGathered flag flipping
+                // counts as a modification. The CASE reads the pre-UPDATE value of the flag.
+                string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered, DateModified = CASE WHEN IFNULL(HasNotesGathered, 0) <> 1 THEN @dateModified ELSE DateModified END WHERE PostID = @PostID AND (IFNULL(HasNotesGathered, 0) <> 1 OR IFNULL(NotesGatheredDateTime, 0) <> @notesGathered)";
                 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                 {
                     command.Parameters.AddWithValue("@notesGathered", DateTimeOffset.UtcNow.ToUnixTimeSeconds());
@@ -1558,49 +1564,60 @@ namespace URLNotesGrabberCORE
             {
                 if (ownsConnection) connection.Open();
 
+                // "." is TraverseDirectory/ReblogRecord's sentinel for "this field had no
+                // matching line in this particular export file" -- not an empty value. A blog
+                // with two export files in different formats (e.g. an "_2" duplicate folder, or
+                // a Tumblr export whose field set changed over time) sends one record with a real
+                // Title and another with Title = "." for the same PostID, and re-importing both
+                // on every run must not let the "not supplied" record blank out what the other
+                // one has. Every content field below is CASE-guarded the same way RootBlogName/
+                // RootURL already were, and the change-detection ignores "." too so a "."-only
+                // difference doesn't fire the UPDATE (and bump DateModified) on its own. Only "."
+                // is treated as the sentinel -- an explicit empty string from a real field still
+                // overwrites, same as before.
                 string sql = "UPDATE Posts SET ";
-                sql += "postDate = @postDate, ";
-                sql += "reblogURL = @reblogURL, ";
-                sql += "postURL = @postURL, ";
-                sql += "slug = @slug, ";
-                sql += "reblogKey = @reblogKey, ";
-                sql += "reblogName = @reblogName, ";
-                sql += "summary = @summary, ";
-                sql += "quote = @quote, ";
-                sql += "body = @body, ";
-                sql += "tags = @tags, ";
-                sql += "link = @link, ";
-                sql += "photoURL = @photoURL, ";
-                sql += "photoCaption = @photoCaption, ";
-                sql += "downloadedFiles = @downloadedFiles, ";
-                sql += "audioCaption = @audioCaption, ";
-                sql += "question = @question, ";
-                sql += "answer = @answer, ";
-                sql += "title = @title, ";
+                sql += "postDate = CASE WHEN @postDate = '.' THEN postDate ELSE @postDate END, ";
+                sql += "reblogURL = CASE WHEN @reblogURL = '.' THEN reblogURL ELSE @reblogURL END, ";
+                sql += "postURL = CASE WHEN @postURL = '.' THEN postURL ELSE @postURL END, ";
+                sql += "slug = CASE WHEN @slug = '.' THEN slug ELSE @slug END, ";
+                sql += "reblogKey = CASE WHEN @reblogKey = '.' THEN reblogKey ELSE @reblogKey END, ";
+                sql += "reblogName = CASE WHEN @reblogName = '.' THEN reblogName ELSE @reblogName END, ";
+                sql += "summary = CASE WHEN @summary = '.' THEN summary ELSE @summary END, ";
+                sql += "quote = CASE WHEN @quote = '.' THEN quote ELSE @quote END, ";
+                sql += "body = CASE WHEN @body = '.' THEN body ELSE @body END, ";
+                sql += "tags = CASE WHEN @tags = '.' THEN tags ELSE @tags END, ";
+                sql += "link = CASE WHEN @link = '.' THEN link ELSE @link END, ";
+                sql += "photoURL = CASE WHEN @photoURL = '.' THEN photoURL ELSE @photoURL END, ";
+                sql += "photoCaption = CASE WHEN @photoCaption = '.' THEN photoCaption ELSE @photoCaption END, ";
+                sql += "downloadedFiles = CASE WHEN @downloadedFiles = '.' THEN downloadedFiles ELSE @downloadedFiles END, ";
+                sql += "audioCaption = CASE WHEN @audioCaption = '.' THEN audioCaption ELSE @audioCaption END, ";
+                sql += "question = CASE WHEN @question = '.' THEN question ELSE @question END, ";
+                sql += "answer = CASE WHEN @answer = '.' THEN answer ELSE @answer END, ";
+                sql += "title = CASE WHEN @title = '.' THEN title ELSE @title END, ";
                 sql += "DateModified = @dateModified, ";
                 sql += "RootBlogName = CASE WHEN @rootBlogName IS NULL OR @rootBlogName = '' OR @rootBlogName = '.' THEN RootBlogName ELSE @rootBlogName END, ";
                 sql += "RootURL = CASE WHEN @rootURL IS NULL OR @rootURL = '' OR @rootURL = '.' THEN RootURL ELSE @rootURL END, ";
                 sql += "hasImage = @hasImage, ";
                 sql += "ByLikes = MAX(IFNULL(ByLikes, 0), @byLikes) ";
                 sql += " WHERE BlogName = @BlogName AND PostID = @PostID AND (";
-                sql += "IFNULL(postDate, '') <> @postDate OR ";
-                sql += "IFNULL(reblogURL, '') <> @reblogURL OR ";
-                sql += "IFNULL(postURL, '') <> @postURL OR ";
-                sql += "IFNULL(slug, '') <> @slug OR ";
-                sql += "IFNULL(reblogKey, '') <> @reblogKey OR ";
-                sql += "IFNULL(reblogName, '') <> @reblogName OR ";
-                sql += "IFNULL(summary, '') <> @summary OR ";
-                sql += "IFNULL(quote, '') <> @quote OR ";
-                sql += "IFNULL(body, '') <> @body OR ";
-                sql += "IFNULL(tags, '') <> @tags OR ";
-                sql += "IFNULL(link, '') <> @link OR ";
-                sql += "IFNULL(photoURL, '') <> @photoURL OR ";
-                sql += "IFNULL(photoCaption, '') <> @photoCaption OR ";
-                sql += "IFNULL(downloadedFiles, '') <> @downloadedFiles OR ";
-                sql += "IFNULL(audioCaption, '') <> @audioCaption OR ";
-                sql += "IFNULL(question, '') <> @question OR ";
-                sql += "IFNULL(answer, '') <> @answer OR ";
-                sql += "IFNULL(title, '') <> @title OR ";
+                sql += "(@postDate <> '.' AND IFNULL(postDate, '') <> @postDate) OR ";
+                sql += "(@reblogURL <> '.' AND IFNULL(reblogURL, '') <> @reblogURL) OR ";
+                sql += "(@postURL <> '.' AND IFNULL(postURL, '') <> @postURL) OR ";
+                sql += "(@slug <> '.' AND IFNULL(slug, '') <> @slug) OR ";
+                sql += "(@reblogKey <> '.' AND IFNULL(reblogKey, '') <> @reblogKey) OR ";
+                sql += "(@reblogName <> '.' AND IFNULL(reblogName, '') <> @reblogName) OR ";
+                sql += "(@summary <> '.' AND IFNULL(summary, '') <> @summary) OR ";
+                sql += "(@quote <> '.' AND IFNULL(quote, '') <> @quote) OR ";
+                sql += "(@body <> '.' AND IFNULL(body, '') <> @body) OR ";
+                sql += "(@tags <> '.' AND IFNULL(tags, '') <> @tags) OR ";
+                sql += "(@link <> '.' AND IFNULL(link, '') <> @link) OR ";
+                sql += "(@photoURL <> '.' AND IFNULL(photoURL, '') <> @photoURL) OR ";
+                sql += "(@photoCaption <> '.' AND IFNULL(photoCaption, '') <> @photoCaption) OR ";
+                sql += "(@downloadedFiles <> '.' AND IFNULL(downloadedFiles, '') <> @downloadedFiles) OR ";
+                sql += "(@audioCaption <> '.' AND IFNULL(audioCaption, '') <> @audioCaption) OR ";
+                sql += "(@question <> '.' AND IFNULL(question, '') <> @question) OR ";
+                sql += "(@answer <> '.' AND IFNULL(answer, '') <> @answer) OR ";
+                sql += "(@title <> '.' AND IFNULL(title, '') <> @title) OR ";
                 sql += "IFNULL(hasImage, 0) <> @hasImage OR ";
                 sql += "(@byLikes = 1 AND IFNULL(ByLikes, 0) = 0) OR ";
                 sql += "((@rootBlogName IS NOT NULL AND @rootBlogName <> '' AND @rootBlogName <> '.') AND IFNULL(RootBlogName, '') <> @rootBlogName) OR ";
@@ -1713,7 +1730,8 @@ namespace URLNotesGrabberCORE
                 string sql = @"UPDATE Blogs
                                   SET LikesNewestTimestamp = MAX(COALESCE(LikesNewestTimestamp, 0), @newest),
                                       DateModified = @modified
-                                WHERE BlogName = @name";
+                                WHERE BlogName = @name
+                                  AND COALESCE(LikesNewestTimestamp, 0) < @newest";
                 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                 {
                     command.Parameters.AddWithValue("@newest", newestTimestamp);
@@ -1809,7 +1827,7 @@ namespace URLNotesGrabberCORE
                 //string sql = "UPDATE Notes SET replyText = @replyText WHERE rootBlogName = @rootBlogName AND PostID = @PostID AND noteBlogName = @noteBlogName AND TimeStamp = @TimeStamp AND Type = 'reply'";
                 // Match on (noteBlogName, TimeStamp ±5s) only - a reply by a given blog at a given timestamp is the same reply across the original post and every reblog of it, so this fans out across reblog chains in one shot. Tolerance absorbs the ~1s drift between what -collect stored and what mode=conversation returns now.
                 // Only fan out to rows that match the SELECT criteria in GetRepliesWithFilledText (NULL/empty/legacy-'.'). Never overwrite '?' (confirmed-empty) or already-fetched text.
-                string sql = "UPDATE Notes SET replyText = @replyText, DateModified = @dateModified WHERE noteBlogName = @noteBlogName AND ABS(TimeStamp - @TimeStamp) <= 5 AND Type = 'reply' AND (replyText IS NULL OR replyText = '' OR replyText = '.')";
+                string sql = "UPDATE Notes SET replyText = @replyText, DateModified = @dateModified WHERE noteBlogName = @noteBlogName AND ABS(TimeStamp - @TimeStamp) <= 5 AND Type = 'reply' AND (replyText IS NULL OR replyText = '' OR replyText = '.') AND (replyText IS NULL OR replyText <> @replyText)";
                 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                 {
                     command.Parameters.AddWithValue("@replyText", replyText ?? "?");
@@ -2066,7 +2084,28 @@ namespace URLNotesGrabberCORE
                                 PostType = @PostType,
                                 HasImage = @HasImage,
                                 DateModified = @DateModified
-                                WHERE BlogName = @BlogName AND PostID = @PostID";
+                                WHERE BlogName = @BlogName AND PostID = @PostID AND (
+                                    IFNULL(reblogURL, '') <> IFNULL(@reblogURL, '') OR
+                                    IFNULL(PostDate, '') <> IFNULL(@PostDate, '') OR
+                                    IFNULL(PostURL, '') <> IFNULL(@PostURL, '') OR
+                                    IFNULL(Slug, '') <> IFNULL(@Slug, '') OR
+                                    IFNULL(ReblogKey, '') <> IFNULL(@ReblogKey, '') OR
+                                    IFNULL(ReblogName, '') <> IFNULL(@ReblogName, '') OR
+                                    IFNULL(Summary, '') <> IFNULL(@Summary, '') OR
+                                    IFNULL(Quote, '') <> IFNULL(@Quote, '') OR
+                                    IFNULL(Body, '') <> IFNULL(@Body, '') OR
+                                    IFNULL(Tags, '') <> IFNULL(@Tags, '') OR
+                                    IFNULL(Link, '') <> IFNULL(@Link, '') OR
+                                    IFNULL(PhotoURL, '') <> IFNULL(@PhotoURL, '') OR
+                                    IFNULL(PhotoCaption, '') <> IFNULL(@PhotoCaption, '') OR
+                                    IFNULL(DownloadedFiles, '') <> IFNULL(@DownloadedFiles, '') OR
+                                    IFNULL(AudioCaption, '') <> IFNULL(@AudioCaption, '') OR
+                                    IFNULL(Question, '') <> IFNULL(@Question, '') OR
+                                    IFNULL(Answer, '') <> IFNULL(@Answer, '') OR
+                                    IFNULL(Title, '') <> IFNULL(@Title, '') OR
+                                    IFNULL(PostType, '') <> IFNULL(@PostType, '') OR
+                                    IFNULL(HasImage, 0) <> @HasImage
+                                )";
 
                     using (var cmd = new SQLiteCommand(updateSql, connection))
                     {
@@ -2258,7 +2297,7 @@ namespace URLNotesGrabberCORE
             using var connection = new SQLiteConnection("Data Source=" + DBPath);
             connection.Open();
             using var cmd = new SQLiteCommand(
-                "UPDATE Blogs SET TTFolderPath = @path, DateModified = @modified WHERE BlogName = @name",
+                "UPDATE Blogs SET TTFolderPath = @path, DateModified = @modified WHERE BlogName = @name AND IFNULL(TTFolderPath, '') <> IFNULL(@path, '')",
                 connection);
             cmd.Parameters.AddWithValue("@path", (object?)path ?? DBNull.Value);
             cmd.Parameters.AddWithValue("@modified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
@@ -2270,12 +2309,14 @@ namespace URLNotesGrabberCORE
         // ThreeTxtFileHelper prefix names ("Reblog URL", "Body", etc.) to non-empty
         // values pulled from a BAK file. Only those columns + DateModified are written;
         // other content columns and all engagement columns are left intact.
-        // Returns true if a row was matched (and therefore updated).
+        // Returns true if a row was actually changed. A row whose columns already hold
+        // the incoming values is left alone, DateModified included.
         public static bool UpdatePostContentFields(string blogName, string postId, IDictionary<string, string> fieldsToUpdate, string? DBPath = null)
         {
             DBPath ??= GetDefaultDbPath();
 
             var setClauses = new List<string>();
+            var changedClauses = new List<string>();
             var parameters = new List<(string Name, object Value)>();
 
             foreach (var kvp in fieldsToUpdate)
@@ -2285,6 +2326,7 @@ namespace URLNotesGrabberCORE
                 if (column == null) continue;
                 string paramName = "@p" + parameters.Count;
                 setClauses.Add($"{column} = {paramName}");
+                changedClauses.Add($"IFNULL({column}, '') <> {paramName}");
                 parameters.Add((paramName, kvp.Value));
             }
 
@@ -2296,7 +2338,7 @@ namespace URLNotesGrabberCORE
             using var connection = new SQLiteConnection("Data Source=" + DBPath);
             connection.Open();
 
-            string sql = $"UPDATE Posts SET {string.Join(", ", setClauses)} WHERE BlogName = @BlogName AND PostID = @PostID";
+            string sql = $"UPDATE Posts SET {string.Join(", ", setClauses)} WHERE BlogName = @BlogName AND PostID = @PostID AND ({string.Join(" OR ", changedClauses)})";
             using var cmd = new SQLiteCommand(sql, connection);
             foreach (var (name, value) in parameters)
                 cmd.Parameters.AddWithValue(name, value);