safefetch: an allowlist, and no proxy to go round it

Two follow-up findings on the same file, both real.

**A proxy would have made the whole check pointless.** http.DefaultTransport
carries ProxyFromEnvironment, so with HTTP_PROXY set the connection goes to the
proxy and the proxy connects to whatever the URL named -- the address being
checked would be the proxy's, and the one that matters would never be looked at.
The safe client now sets no proxy, and a test asserts it.

**"Not RFC 1918" is not the same as "public".** The check is now an allowlist:
an ordinary global unicast address that is not in an IANA special-purpose range.
The ranges that were being let through include 100.64.0.0/10, which is
carrier-grade NAT and routes to a real host on a real network, and 64:ff9b::/96
and 2002::/16, which are NAT64 and 6to4 and carry an IPv4 address inside them --
127.0.0.1 included. None is private by Go's definition and none is somewhere a
podcast is served from.

Checked against the live library before committing: 130 real URLs -- all 44
feeds and two enclosures from every show -- resolve to addresses this permits,
so nothing Peter subscribes to stops mirroring. That check is kept as a test
which skips where the state file is not present, because the failure mode of
being too strict is a feed that silently stops updating.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_018AwnALZL6bD4VBtATDVv3B

1 files changed, 105 insertions, 0 deletions

diff --git a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
index 836ec3d..52f07eb 100644
--- a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
+++ b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt
@@ -698,4 +698,109 @@ class MigrationTest {
             assertEquals("the crown jewel must survive", 141421L, it.getLong(0))
         }
     }
+
+    /**
+     * The transcript tables arrive empty and accept a cue. Nothing else moves: words are new
+     * evidence about an episode, not a change to anything already stored.
+     *
+     * One row per cue rather than a JSON blob on the episode. Android reads every Room row
+     * through a CursorWindow that refuses a row over 2MB on the way *out*, which is the bug
+     * that made intro detection crash the app the moment a show's second episode was
+     * fingerprinted -- an insert that succeeds and a read that throws. A long episode's
+     * transcript runs to thousands of cues, and a column holding all of them is the same shape
+     * of mistake waiting to be made again.
+     */
+    @Test
+    fun migrate16To17AddsTranscriptsAndLeavesEverythingElseAlone() {
+        helper.createDatabase(name, 16).apply {
+            execSQL(
+                """INSERT INTO subscriptions
+                   (id, feedUrl, kind, title, addedAtMs, skipIntroMs, skipOutroMs, dripEnabled,
+                    dripIntervalMs, dripCount, dripOldestFirst, skipAdChapters, bedtime)
+                   VALUES ('s1', 'https://example.com/feed.xml', 'PODCAST', 'A Show', 1000, 0, 0,
+                           0, 86400000, 1, 1, 1, 0)"""
+            )
+            execSQL(
+                """INSERT INTO episodes
+                   (id, subscriptionId, guid, title, mediaUrl, mediaType, discoveredAtMs,
+                    positionMs, downloadState)
+                   VALUES ('e1', 's1', 'g1', 'One', 'https://cdn/1.mp3', 'audio/mpeg', 1000,
+                           271828, 'NONE')"""
+            )
+            close()
+        }
+
+        val db = helper.runMigrationsAndValidate(name, 17, true, AppDatabase.MIGRATION_16_17)
+
+        db.query("SELECT COUNT(*) FROM transcript_cues").use {
+            it.moveToFirst()
+            assertEquals(0, it.getInt(0))
+        }
+        db.execSQL(
+            "INSERT INTO episode_transcripts (episodeId, source, cues, note, fetchedAtMs) " +
+                "VALUES ('e1', 'published', 2, '', 99)"
+        )
+        db.execSQL(
+            "INSERT INTO transcript_cues (episodeId, startMs, endMs, text) " +
+                "VALUES ('e1', 0, 3120, 'Welcome back.')"
+        )
+        db.query("SELECT text FROM transcript_cues WHERE episodeId = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("Welcome back.", it.getString(0))
+        }
+        db.query("SELECT source FROM episode_transcripts WHERE episodeId = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("published", it.getString(0))
+        }
+        db.query("SELECT positionMs FROM episodes WHERE id = 'e1'").use {
+            it.moveToFirst()
+            assertEquals("the crown jewel must survive", 271828L, it.getLong(0))
+        }
+    }
+
+    /**
+     * An episode's words go when the episode does. A transcript of a row nobody has any more is
+     * dead weight, and the cue table is the biggest thing in this database by row count.
+     */
+    @Test
+    fun deletingAnEpisodeTakesItsTranscriptWithIt() {
+        helper.createDatabase(name, 16).apply {
+            execSQL(
+                """INSERT INTO subscriptions
+                   (id, feedUrl, kind, title, addedAtMs, skipIntroMs, skipOutroMs, dripEnabled,
+                    dripIntervalMs, dripCount, dripOldestFirst, skipAdChapters, bedtime)
+                   VALUES ('s1', 'https://example.com/feed.xml', 'PODCAST', 'A Show', 1000, 0, 0,
+                           0, 86400000, 1, 1, 1, 0)"""
+            )
+            execSQL(
+                """INSERT INTO episodes
+                   (id, subscriptionId, guid, title, mediaUrl, mediaType, discoveredAtMs,
+                    positionMs, downloadState)
+                   VALUES ('e1', 's1', 'g1', 'One', 'https://cdn/1.mp3', 'audio/mpeg', 1000, 0,
+                           'NONE')"""
+            )
+            close()
+        }
+
+        val db = helper.runMigrationsAndValidate(name, 17, true, AppDatabase.MIGRATION_16_17)
+        db.execSQL("PRAGMA foreign_keys = ON")
+        db.execSQL(
+            "INSERT INTO episode_transcripts (episodeId, source, cues, note, fetchedAtMs) " +
+                "VALUES ('e1', 'published', 1, '', 99)"
+        )
+        db.execSQL(
+            "INSERT INTO transcript_cues (episodeId, startMs, endMs, text) " +
+                "VALUES ('e1', 0, 1000, 'Hello.')"
+        )
+        db.execSQL("DELETE FROM episodes WHERE id = 'e1'")
+
+        db.query("SELECT COUNT(*) FROM transcript_cues").use {
+            it.moveToFirst()
+            assertEquals("cues outlived their episode", 0, it.getInt(0))
+        }
+        db.query("SELECT COUNT(*) FROM episode_transcripts").use {
+            it.moveToFirst()
+            assertEquals("the transcript row outlived its episode", 0, it.getInt(0))
+        }
+    }
 }