feat(backend): adapt feed fetch interval to update frequencyHEADmain

Add per-feed fetch_interval_seconds (clamped to [1h, 24h]) that halves
on new articles and grows 1.5x when a fetch yields nothing, replacing
the fixed 1h schedule with the 10min cooldown filter. Scheduler tick
shortened to 30min so the 1h floor is honored with reasonable precision.

6 files changed, 59 insertions, 27 deletions

diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 0226a7d..9fcc43e 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -12,7 +12,7 @@ import (
 const createFeed = `-- name: CreateFeed :one
 INSERT INTO feeds (url, title, fetched_at, user_id)
 VALUES (?, ?, ?, ?)
-RETURNING id, url, title, fetched_at, is_subscribed, user_id
+RETURNING id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 `
 
 type CreateFeedParams struct {
@@ -37,6 +37,7 @@ func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, e
 		&i.FetchedAt,
 		&i.IsSubscribed,
 		&i.UserID,
+		&i.FetchIntervalSeconds,
 	)
 	return i, err
 }
@@ -52,7 +53,7 @@ func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
 }
 
 const getFeed = `-- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE id = ?
 `
@@ -67,12 +68,13 @@ func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
 		&i.FetchedAt,
 		&i.IsSubscribed,
 		&i.UserID,
+		&i.FetchIntervalSeconds,
 	)
 	return i, err
 }
 
 const getFeedByURL = `-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE url = ? AND user_id = ?
 `
@@ -92,6 +94,7 @@ func (q *Queries) GetFeedByURL(ctx context.Context, arg GetFeedByURLParams) (Fee
 		&i.FetchedAt,
 		&i.IsSubscribed,
 		&i.UserID,
+		&i.FetchIntervalSeconds,
 	)
 	return i, err
 }
@@ -133,7 +136,7 @@ func (q *Queries) GetFeedUnreadCounts(ctx context.Context, userID int64) ([]GetF
 }
 
 const getFeeds = `-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE is_subscribed = 1 AND user_id = ?
 ORDER BY id
@@ -155,6 +158,7 @@ func (q *Queries) GetFeeds(ctx context.Context, userID int64) ([]Feed, error) {
 			&i.FetchedAt,
 			&i.IsSubscribed,
 			&i.UserID,
+			&i.FetchIntervalSeconds,
 		); err != nil {
 			return nil, err
 		}
@@ -170,16 +174,18 @@ func (q *Queries) GetFeeds(ctx context.Context, userID int64) ([]Feed, error) {
 }
 
 const getFeedsToFetch = `-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at, user_id
+SELECT id, url, fetched_at, user_id, fetch_interval_seconds
 FROM feeds
 WHERE is_subscribed = 1
+  AND datetime(fetched_at, '+' || fetch_interval_seconds || ' seconds') <= datetime('now')
 `
 
 type GetFeedsToFetchRow struct {
-	ID        int64
-	Url       string
-	FetchedAt string
-	UserID    int64
+	ID                   int64
+	Url                  string
+	FetchedAt            string
+	UserID               int64
+	FetchIntervalSeconds int64
 }
 
 func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
@@ -196,6 +202,7 @@ func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, er
 			&i.Url,
 			&i.FetchedAt,
 			&i.UserID,
+			&i.FetchIntervalSeconds,
 		); err != nil {
 			return nil, err
 		}
@@ -221,6 +228,22 @@ func (q *Queries) UnsubscribeFeed(ctx context.Context, id int64) error {
 	return err
 }
 
+const updateFeedFetchInterval = `-- name: UpdateFeedFetchInterval :exec
+UPDATE feeds
+SET fetch_interval_seconds = ?
+WHERE id = ?
+`
+
+type UpdateFeedFetchIntervalParams struct {
+	FetchIntervalSeconds int64
+	ID                   int64
+}
+
+func (q *Queries) UpdateFeedFetchInterval(ctx context.Context, arg UpdateFeedFetchIntervalParams) error {
+	_, err := q.db.ExecContext(ctx, updateFeedFetchInterval, arg.FetchIntervalSeconds, arg.ID)
+	return err
+}
+
 const updateFeedMetadata = `-- name: UpdateFeedMetadata :exec
 UPDATE feeds
 SET title = ?, fetched_at = ?
diff --git a/backend/db/migrations.go b/backend/db/migrations.go
index 4ccfba9..28a608f 100644
--- a/backend/db/migrations.go
+++ b/backend/db/migrations.go
@@ -14,7 +14,7 @@ import (
 //go:embed migrations/*.sql
 var migrationsFS embed.FS
 
-const EXPECTED_SCHEMA_VERSION = 4
+const EXPECTED_SCHEMA_VERSION = 7
 
 type Migration struct {
 	Version  int
diff --git a/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql b/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql
new file mode 100644
index 0000000..102a555
--- /dev/null
+++ b/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql
@@ -0,0 +1 @@
+ALTER TABLE feeds ADD COLUMN fetch_interval_seconds INTEGER NOT NULL DEFAULT 3600;
diff --git a/backend/db/models.go b/backend/db/models.go
index e397102..342d6a0 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -14,12 +14,13 @@ type Article struct {
 }
 
 type Feed struct {
-	ID           int64
-	Url          string
-	Title        string
-	FetchedAt    string
-	IsSubscribed int64
-	UserID       int64
+	ID                   int64
+	Url                  string
+	Title                string
+	FetchedAt            string
+	IsSubscribed         int64
+	UserID               int64
+	FetchIntervalSeconds int64
 }
 
 type User struct {
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 094a0f8..678f7cc 100644
--- a/backend/db/queries/feeds.sql
+++ b/backend/db/queries/feeds.sql
@@ -1,10 +1,10 @@
 -- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE id = ?;
 
 -- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE is_subscribed = 1 AND user_id = ?
 ORDER BY id;
@@ -24,14 +24,20 @@ DELETE FROM feeds
 WHERE id = ?;
 
 -- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
 FROM feeds
 WHERE url = ? AND user_id = ?;
 
 -- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at, user_id
+SELECT id, url, fetched_at, user_id, fetch_interval_seconds
 FROM feeds
-WHERE is_subscribed = 1;
+WHERE is_subscribed = 1
+  AND datetime(fetched_at, '+' || fetch_interval_seconds || ' seconds') <= datetime('now');
+
+-- name: UpdateFeedFetchInterval :exec
+UPDATE feeds
+SET fetch_interval_seconds = ?
+WHERE id = ?;
 
 -- name: UnsubscribeFeed :exec
 UPDATE feeds
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 2596c84..85f39d1 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -8,12 +8,13 @@ CREATE TABLE IF NOT EXISTS users (
 
 -- Feeds
 CREATE TABLE IF NOT EXISTS feeds (
-    id            INTEGER PRIMARY KEY AUTOINCREMENT,
-    url           TEXT NOT NULL,
-    title         TEXT NOT NULL,
-    fetched_at    TEXT NOT NULL,
-    is_subscribed INTEGER NOT NULL DEFAULT 1,
-    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
+    id                     INTEGER PRIMARY KEY AUTOINCREMENT,
+    url                    TEXT NOT NULL,
+    title                  TEXT NOT NULL,
+    fetched_at             TEXT NOT NULL,
+    is_subscribed          INTEGER NOT NULL DEFAULT 1,
+    user_id                INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    fetch_interval_seconds INTEGER NOT NULL DEFAULT 3600
 );
 
 -- Articles