From ba1e0c904f810193f25d4f88cc2bb168f1d625fe Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Mon, 27 Oct 2025 03:00:27 +0900
Subject: feat(backend): Make feeds.user_id column non-null

---
 backend/db/feeds.sql.go                            | 15 +++++---
 backend/db/migrations.go                           |  2 +-
 .../migrations/004_make_feeds_user_id_not_null.sql | 40 ++++++++++++++++++++++
 backend/db/models.go                               |  6 +---
 backend/db/queries/feeds.sql                       |  4 +--
 backend/db/schema.sql                              |  2 +-
 backend/graphql/resolver/schema.resolvers.go       |  1 +
 7 files changed, 56 insertions(+), 14 deletions(-)
 create mode 100644 backend/db/migrations/004_make_feeds_user_id_not_null.sql

(limited to 'backend')

diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 9d6c5c1..140fa3a 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -7,12 +7,11 @@ package db
 
 import (
 	"context"
-	"database/sql"
 )
 
 const createFeed = `-- name: CreateFeed :one
-INSERT INTO feeds (url, title, fetched_at)
-VALUES (?, ?, ?)
+INSERT INTO feeds (url, title, fetched_at, user_id)
+VALUES (?, ?, ?, ?)
 RETURNING id, url, title, fetched_at, is_subscribed, user_id
 `
 
@@ -20,10 +19,16 @@ type CreateFeedParams struct {
 	Url       string
 	Title     string
 	FetchedAt string
+	UserID    int64
 }
 
 func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, error) {
-	row := q.db.QueryRowContext(ctx, createFeed, arg.Url, arg.Title, arg.FetchedAt)
+	row := q.db.QueryRowContext(ctx, createFeed,
+		arg.Url,
+		arg.Title,
+		arg.FetchedAt,
+		arg.UserID,
+	)
 	var i Feed
 	err := row.Scan(
 		&i.ID,
@@ -133,7 +138,7 @@ type GetFeedsToFetchRow struct {
 	ID        int64
 	Url       string
 	FetchedAt string
-	UserID    sql.NullInt64
+	UserID    int64
 }
 
 func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
diff --git a/backend/db/migrations.go b/backend/db/migrations.go
index 41c837c..4ccfba9 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 = 3
+const EXPECTED_SCHEMA_VERSION = 4
 
 type Migration struct {
 	Version  int
diff --git a/backend/db/migrations/004_make_feeds_user_id_not_null.sql b/backend/db/migrations/004_make_feeds_user_id_not_null.sql
new file mode 100644
index 0000000..0d2f12e
--- /dev/null
+++ b/backend/db/migrations/004_make_feeds_user_id_not_null.sql
@@ -0,0 +1,40 @@
+-- Make feeds.user_id NOT NULL
+--
+-- SQLite does not support ALTER COLUMN directly, so we need to:
+--  1. Temporarily disable foreign key constraints
+--  2. Create a new table with the correct schema
+--  3. Copy data from the old table
+--  4. Drop the old table
+--  5. Rename the new table
+--  6. Re-enable foreign key constraints
+
+-- Disable foreign key constraints temporarily
+PRAGMA foreign_keys=OFF;
+
+-- Create new feeds table with user_id NOT NULL
+CREATE TABLE feeds_new (
+    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
+);
+
+-- Copy data from old table (only rows with non-null user_id)
+INSERT INTO feeds_new (id, url, title, fetched_at, is_subscribed, user_id)
+SELECT id, url, title, fetched_at, is_subscribed, user_id
+FROM feeds
+WHERE user_id IS NOT NULL;
+
+-- Drop old table (without cascading to articles since FK is disabled)
+DROP TABLE feeds;
+
+-- Rename new table
+ALTER TABLE feeds_new RENAME TO feeds;
+
+-- Recreate index
+CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);
+
+-- Re-enable foreign key constraints
+PRAGMA foreign_keys=ON;
diff --git a/backend/db/models.go b/backend/db/models.go
index 81fcd0c..e397102 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -4,10 +4,6 @@
 
 package db
 
-import (
-	"database/sql"
-)
-
 type Article struct {
 	ID     int64
 	FeedID int64
@@ -23,7 +19,7 @@ type Feed struct {
 	Title        string
 	FetchedAt    string
 	IsSubscribed int64
-	UserID       sql.NullInt64
+	UserID       int64
 }
 
 type User struct {
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 3b5b882..9725252 100644
--- a/backend/db/queries/feeds.sql
+++ b/backend/db/queries/feeds.sql
@@ -10,8 +10,8 @@ WHERE is_subscribed = 1
 ORDER BY id;
 
 -- name: CreateFeed :one
-INSERT INTO feeds (url, title, fetched_at)
-VALUES (?, ?, ?)
+INSERT INTO feeds (url, title, fetched_at, user_id)
+VALUES (?, ?, ?, ?)
 RETURNING *;
 
 -- name: UpdateFeedMetadata :exec
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index e1260b0..adf3b37 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -13,7 +13,7 @@ CREATE TABLE IF NOT EXISTS feeds (
     title         TEXT NOT NULL,
     fetched_at    TEXT NOT NULL,
     is_subscribed INTEGER NOT NULL DEFAULT 1,
-    user_id       INTEGER REFERENCES users(id) ON DELETE CASCADE
+    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
 );
 
 -- Articles
diff --git a/backend/graphql/resolver/schema.resolvers.go b/backend/graphql/resolver/schema.resolvers.go
index cadcd33..0c811c2 100644
--- a/backend/graphql/resolver/schema.resolvers.go
+++ b/backend/graphql/resolver/schema.resolvers.go
@@ -31,6 +31,7 @@ func (r *mutationResolver) AddFeed(ctx context.Context, url string) (*model.Feed
 		Url:       url,
 		Title:     feed.Title,
 		FetchedAt: time.Now().UTC().Format(time.RFC3339),
+		UserID:    int64(1), // TODO
 	})
 	if err != nil {
 		return nil, fmt.Errorf("failed to insert feed: %w", err)
-- 
cgit v1.2.3-70-g09d2