feat(backend): Create users table

6 files changed, 61 insertions, 12 deletions

diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 29b26ca..9d6c5c1 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -7,12 +7,13 @@ package db
 
 import (
 	"context"
+	"database/sql"
 )
 
 const createFeed = `-- name: CreateFeed :one
 INSERT INTO feeds (url, title, fetched_at)
 VALUES (?, ?, ?)
-RETURNING id, url, title, fetched_at, is_subscribed
+RETURNING id, url, title, fetched_at, is_subscribed, user_id
 `
 
 type CreateFeedParams struct {
@@ -30,6 +31,7 @@ func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, e
 		&i.Title,
 		&i.FetchedAt,
 		&i.IsSubscribed,
+		&i.UserID,
 	)
 	return i, err
 }
@@ -45,7 +47,7 @@ func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
 }
 
 const getFeed = `-- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE id = ?
 `
@@ -59,12 +61,13 @@ func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
 		&i.Title,
 		&i.FetchedAt,
 		&i.IsSubscribed,
+		&i.UserID,
 	)
 	return i, err
 }
 
 const getFeedByURL = `-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE url = ?
 `
@@ -78,12 +81,13 @@ func (q *Queries) GetFeedByURL(ctx context.Context, url string) (Feed, error) {
 		&i.Title,
 		&i.FetchedAt,
 		&i.IsSubscribed,
+		&i.UserID,
 	)
 	return i, err
 }
 
 const getFeeds = `-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE is_subscribed = 1
 ORDER BY id
@@ -104,6 +108,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
 			&i.Title,
 			&i.FetchedAt,
 			&i.IsSubscribed,
+			&i.UserID,
 		); err != nil {
 			return nil, err
 		}
@@ -119,7 +124,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
 }
 
 const getFeedsToFetch = `-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at
+SELECT id, url, fetched_at, user_id
 FROM feeds
 WHERE is_subscribed = 1
 `
@@ -128,6 +133,7 @@ type GetFeedsToFetchRow struct {
 	ID        int64
 	Url       string
 	FetchedAt string
+	UserID    sql.NullInt64
 }
 
 func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
@@ -139,7 +145,12 @@ func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, er
 	items := []GetFeedsToFetchRow{}
 	for rows.Next() {
 		var i GetFeedsToFetchRow
-		if err := rows.Scan(&i.ID, &i.Url, &i.FetchedAt); err != nil {
+		if err := rows.Scan(
+			&i.ID,
+			&i.Url,
+			&i.FetchedAt,
+			&i.UserID,
+		); err != nil {
 			return nil, err
 		}
 		items = append(items, i)
diff --git a/backend/db/migrations.go b/backend/db/migrations.go
index 9a146a1..41c837c 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 = 2
+const EXPECTED_SCHEMA_VERSION = 3
 
 type Migration struct {
 	Version  int
diff --git a/backend/db/migrations/003_add_users_table.sql b/backend/db/migrations/003_add_users_table.sql
new file mode 100644
index 0000000..4cf963b
--- /dev/null
+++ b/backend/db/migrations/003_add_users_table.sql
@@ -0,0 +1,15 @@
+-- Add users table and user_id column to feeds table.
+
+-- Users
+CREATE TABLE IF NOT EXISTS users (
+    id            INTEGER PRIMARY KEY AUTOINCREMENT,
+    username      TEXT NOT NULL UNIQUE,
+    password_hash TEXT NOT NULL,
+    created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Add user_id to feeds table
+ALTER TABLE feeds ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;
+
+-- Index for feeds.user_id
+CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);
diff --git a/backend/db/models.go b/backend/db/models.go
index 94ab9c0..81fcd0c 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -4,6 +4,10 @@
 
 package db
 
+import (
+	"database/sql"
+)
+
 type Article struct {
 	ID     int64
 	FeedID int64
@@ -19,4 +23,12 @@ type Feed struct {
 	Title        string
 	FetchedAt    string
 	IsSubscribed int64
+	UserID       sql.NullInt64
+}
+
+type User struct {
+	ID           int64
+	Username     string
+	PasswordHash string
+	CreatedAt    string
 }
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 8445532..3b5b882 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
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE id = ?;
 
 -- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE is_subscribed = 1
 ORDER BY id;
@@ -24,12 +24,12 @@ DELETE FROM feeds
 WHERE id = ?;
 
 -- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
 FROM feeds
 WHERE url = ?;
 
 -- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at
+SELECT id, url, fetched_at, user_id
 FROM feeds
 WHERE is_subscribed = 1;
 
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index eb40dea..e1260b0 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -1,10 +1,19 @@
+-- Users
+CREATE TABLE IF NOT EXISTS users (
+    id            INTEGER PRIMARY KEY AUTOINCREMENT,
+    username      TEXT NOT NULL UNIQUE,
+    password_hash TEXT NOT NULL,
+    created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
 -- 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
+    is_subscribed INTEGER NOT NULL DEFAULT 1,
+    user_id       INTEGER REFERENCES users(id) ON DELETE CASCADE
 );
 
 -- Articles
@@ -24,3 +33,5 @@ CREATE INDEX IF NOT EXISTS idx_articles_feed_id ON articles(feed_id);
 CREATE INDEX IF NOT EXISTS idx_articles_feed_guid ON articles(feed_id, guid);
 
 CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read);
+
+CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);