From a7fbe6d5008decde1e2402b422300d83d3a3de0e Mon Sep 17 00:00:00 2001 From: Paul Buetow Date: Sun, 17 May 2026 21:21:42 +0300 Subject: Add api_tokens table, model, and repository --- player-server/internal/api/handlers_test.go | 15 +++ player-server/internal/model/api_token.go | 14 +++ player-server/internal/repository/api_token.go | 90 +++++++++++++ .../internal/repository/api_token_test.go | 139 +++++++++++++++++++++ player-server/internal/repository/mock.go | 76 +++++++++++ player-server/internal/repository/repository.go | 15 +++ player-server/internal/repository/schema.go | 26 ++++ player-server/internal/repository/sqlite.go | 1 + .../internal/repository/sqlite_no_rows_test.go | 10 ++ 9 files changed, 386 insertions(+) create mode 100644 player-server/internal/model/api_token.go create mode 100644 player-server/internal/repository/api_token.go create mode 100644 player-server/internal/repository/api_token_test.go (limited to 'player-server/internal') diff --git a/player-server/internal/api/handlers_test.go b/player-server/internal/api/handlers_test.go index e900886..6cde80b 100644 --- a/player-server/internal/api/handlers_test.go +++ b/player-server/internal/api/handlers_test.go @@ -1469,6 +1469,21 @@ func (m *mockPingStore) DeleteUser(ctx context.Context, id int64) error { func (m *mockPingStore) CountUsers(ctx context.Context) (int, error) { return m.store.CountUsers(ctx) } +func (m *mockPingStore) Create(ctx context.Context, token *model.APIToken) (int64, error) { + return m.store.Create(ctx, token) +} +func (m *mockPingStore) GetByHash(ctx context.Context, tokenHash string) (*model.APIToken, error) { + return m.store.GetByHash(ctx, tokenHash) +} +func (m *mockPingStore) ListByUser(ctx context.Context, userID int64) ([]model.APIToken, error) { + return m.store.ListByUser(ctx, userID) +} +func (m *mockPingStore) DeleteByID(ctx context.Context, id int64) error { + return m.store.DeleteByID(ctx, id) +} +func (m *mockPingStore) TouchLastUsed(ctx context.Context, id int64, lastUsedAt time.Time) error { + return m.store.TouchLastUsed(ctx, id, lastUsedAt) +} func (m *mockPingStore) CreateSet(ctx context.Context, set *model.Set) (int64, error) { return m.store.CreateSet(ctx, set) } diff --git a/player-server/internal/model/api_token.go b/player-server/internal/model/api_token.go new file mode 100644 index 0000000..f359072 --- /dev/null +++ b/player-server/internal/model/api_token.go @@ -0,0 +1,14 @@ +package model + +import "time" + +// APIToken represents a hashed API authentication token for a user. +type APIToken struct { + ID int64 `json:"id"` + UserID int64 `json:"user_id"` + TokenHash string `json:"-"` + Name string `json:"name"` + LastUsedAt *time.Time `json:"last_used_at"` + ExpiresAt *time.Time `json:"expires_at"` + CreatedAt time.Time `json:"created_at"` +} diff --git a/player-server/internal/repository/api_token.go b/player-server/internal/repository/api_token.go new file mode 100644 index 0000000..ba54f15 --- /dev/null +++ b/player-server/internal/repository/api_token.go @@ -0,0 +1,90 @@ +package repository + +import ( + "context" + "database/sql" + "fmt" + "time" + + "codeberg.org/snonux/player/internal/model" +) + +// Create inserts a new API token and returns the generated ID. +func (s *SQLite) Create(ctx context.Context, token *model.APIToken) (int64, error) { + res, err := s.db.ExecContext(ctx, + `INSERT INTO api_tokens (user_id, token_hash, name, last_used_at, expires_at, created_at) VALUES (?, ?, ?, ?, ?, ?)`, + token.UserID, token.TokenHash, token.Name, sqlNullTime(token.LastUsedAt), sqlNullTime(token.ExpiresAt), token.CreatedAt, + ) + if err != nil { + return 0, fmt.Errorf("insert api token: %w", err) + } + return res.LastInsertId() +} + +func scanAPIToken(row sqlScanner) (*model.APIToken, error) { + var token model.APIToken + var lastUsedAt, expiresAt sql.NullTime + err := row.Scan(&token.ID, &token.UserID, &token.TokenHash, &token.Name, &lastUsedAt, &expiresAt, &token.CreatedAt) + if err == sql.ErrNoRows { + return nil, nil + } + if err != nil { + return nil, err + } + if lastUsedAt.Valid { + token.LastUsedAt = &lastUsedAt.Time + } + if expiresAt.Valid { + token.ExpiresAt = &expiresAt.Time + } + return &token, nil +} + +// GetByHash retrieves an API token by token hash. +func (s *SQLite) GetByHash(ctx context.Context, tokenHash string) (*model.APIToken, error) { + row := s.db.QueryRowContext(ctx, + `SELECT id, user_id, token_hash, name, last_used_at, expires_at, created_at FROM api_tokens WHERE token_hash = ?`, + tokenHash, + ) + return scanAPIToken(row) +} + +// ListByUser returns all API tokens for a user ordered newest first. +func (s *SQLite) ListByUser(ctx context.Context, userID int64) ([]model.APIToken, error) { + rows, err := s.db.QueryContext(ctx, + `SELECT id, user_id, token_hash, name, last_used_at, expires_at, created_at FROM api_tokens WHERE user_id = ? ORDER BY created_at DESC, id DESC`, + userID, + ) + if err != nil { + return nil, fmt.Errorf("list api tokens by user: %w", err) + } + defer rows.Close() + + var tokens []model.APIToken + for rows.Next() { + token, err := scanAPIToken(rows) + if err != nil { + return nil, err + } + tokens = append(tokens, *token) + } + return tokens, rows.Err() +} + +// DeleteByID removes an API token by database ID. +func (s *SQLite) DeleteByID(ctx context.Context, id int64) error { + _, err := s.db.ExecContext(ctx, `DELETE FROM api_tokens WHERE id = ?`, id) + if err != nil { + return fmt.Errorf("delete api token: %w", err) + } + return nil +} + +// TouchLastUsed updates an API token's last-used timestamp. +func (s *SQLite) TouchLastUsed(ctx context.Context, id int64, lastUsedAt time.Time) error { + _, err := s.db.ExecContext(ctx, `UPDATE api_tokens SET last_used_at = ? WHERE id = ?`, lastUsedAt, id) + if err != nil { + return fmt.Errorf("touch api token last used: %w", err) + } + return nil +} diff --git a/player-server/internal/repository/api_token_test.go b/player-server/internal/repository/api_token_test.go new file mode 100644 index 0000000..fe1e923 --- /dev/null +++ b/player-server/internal/repository/api_token_test.go @@ -0,0 +1,139 @@ +package repository + +import ( + "context" + "testing" + "time" + + "codeberg.org/snonux/player/internal/model" +) + +func TestSQLite_APITokenRepo(t *testing.T) { + ctx := context.Background() + s := newTestStore(t) + defer s.Close() + + now := time.Date(2026, 5, 17, 12, 0, 0, 0, time.UTC) + userID := mustCreateAPITokenUser(t, ctx, s, "api-user") + otherUserID := mustCreateAPITokenUser(t, ctx, s, "other-api-user") + + expiresAt := now.Add(24 * time.Hour) + firstID, err := s.Create(ctx, &model.APIToken{ + UserID: userID, + TokenHash: "hash-one", + Name: "first token", + ExpiresAt: &expiresAt, + CreatedAt: now, + }) + if err != nil { + t.Fatalf("create first token: %v", err) + } + + got, err := s.GetByHash(ctx, "hash-one") + if err != nil { + t.Fatalf("get by hash: %v", err) + } + if got == nil { + t.Fatal("expected token, got nil") + } + assertAPIToken(t, got, firstID, userID, "hash-one", "first token", nil, &expiresAt, now) + + lastUsedAt := now.Add(time.Hour) + secondID, err := s.Create(ctx, &model.APIToken{ + UserID: userID, + TokenHash: "hash-two", + Name: "second token", + LastUsedAt: &lastUsedAt, + CreatedAt: now.Add(time.Minute), + }) + if err != nil { + t.Fatalf("create second token: %v", err) + } + if _, err := s.Create(ctx, &model.APIToken{ + UserID: otherUserID, + TokenHash: "hash-other", + Name: "other token", + CreatedAt: now.Add(2 * time.Minute), + }); err != nil { + t.Fatalf("create other user token: %v", err) + } + + tokens, err := s.ListByUser(ctx, userID) + if err != nil { + t.Fatalf("list by user: %v", err) + } + if len(tokens) != 2 { + t.Fatalf("expected 2 tokens, got %d", len(tokens)) + } + if tokens[0].ID != secondID || tokens[1].ID != firstID { + t.Fatalf("unexpected list order: %+v", tokens) + } + + touchedAt := now.Add(2 * time.Hour) + if err := s.TouchLastUsed(ctx, firstID, touchedAt); err != nil { + t.Fatalf("touch last used: %v", err) + } + got, err = s.GetByHash(ctx, "hash-one") + if err != nil { + t.Fatalf("get touched token: %v", err) + } + assertTimePtr(t, got.LastUsedAt, &touchedAt, "last used after touch") + + if err := s.DeleteByID(ctx, firstID); err != nil { + t.Fatalf("delete by id: %v", err) + } + got, err = s.GetByHash(ctx, "hash-one") + if err != nil { + t.Fatalf("get deleted token: %v", err) + } + if got != nil { + t.Fatalf("expected deleted token to be nil, got %+v", got) + } +} + +func mustCreateAPITokenUser(t *testing.T, ctx context.Context, s *SQLite, username string) int64 { + t.Helper() + id, err := s.CreateUser(ctx, &model.User{ + Username: username, + PasswordHash: "hash", + CreatedAt: time.Date(2026, 5, 17, 11, 0, 0, 0, time.UTC), + }) + if err != nil { + t.Fatalf("create user %q: %v", username, err) + } + return id +} + +func assertAPIToken(t *testing.T, token *model.APIToken, id, userID int64, tokenHash, name string, lastUsedAt, expiresAt *time.Time, createdAt time.Time) { + t.Helper() + if token.ID != id { + t.Fatalf("expected id %d, got %d", id, token.ID) + } + if token.UserID != userID { + t.Fatalf("expected user id %d, got %d", userID, token.UserID) + } + if token.TokenHash != tokenHash { + t.Fatalf("expected token hash %q, got %q", tokenHash, token.TokenHash) + } + if token.Name != name { + t.Fatalf("expected name %q, got %q", name, token.Name) + } + assertTimePtr(t, token.LastUsedAt, lastUsedAt, "last used") + assertTimePtr(t, token.ExpiresAt, expiresAt, "expires") + if !token.CreatedAt.Equal(createdAt) { + t.Fatalf("expected created at %s, got %s", createdAt, token.CreatedAt) + } +} + +func assertTimePtr(t *testing.T, got, want *time.Time, field string) { + t.Helper() + if got == nil || want == nil { + if got != want { + t.Fatalf("expected %s %v, got %v", field, want, got) + } + return + } + if !got.Equal(*want) { + t.Fatalf("expected %s %s, got %s", field, *want, *got) + } +} diff --git a/player-server/internal/repository/mock.go b/player-server/internal/repository/mock.go index 994fca5..de330ff 100644 --- a/player-server/internal/repository/mock.go +++ b/player-server/internal/repository/mock.go @@ -27,6 +27,7 @@ var ( _ TrashServiceStore = (*MockStore)(nil) _ UserAdminServiceStore = (*MockStore)(nil) _ PermissionAdminServiceStore = (*MockStore)(nil) + _ APITokenRepo = (*MockStore)(nil) _ PodcastRepo = (*MockStore)(nil) ) @@ -40,6 +41,7 @@ func NewMockStore() *MockStore { // callers override individual func fields to inject test behavior. type MockStore struct { UserRepo MockUserRepo + APITokenRepo MockAPITokenRepo SetRepo MockSetRepo SetPermissionRepo MockSetPermissionRepo MediaRepo MockMediaRepo @@ -81,6 +83,31 @@ func (m *MockStore) DeleteUser(ctx context.Context, id int64) error { // CountUsers implements UserRepo. func (m *MockStore) CountUsers(ctx context.Context) (int, error) { return m.UserRepo.CountUsers(ctx) } +// Create implements APITokenRepo. +func (m *MockStore) Create(ctx context.Context, token *model.APIToken) (int64, error) { + return m.APITokenRepo.Create(ctx, token) +} + +// GetByHash implements APITokenRepo. +func (m *MockStore) GetByHash(ctx context.Context, tokenHash string) (*model.APIToken, error) { + return m.APITokenRepo.GetByHash(ctx, tokenHash) +} + +// ListByUser implements APITokenRepo. +func (m *MockStore) ListByUser(ctx context.Context, userID int64) ([]model.APIToken, error) { + return m.APITokenRepo.ListByUser(ctx, userID) +} + +// DeleteByID implements APITokenRepo. +func (m *MockStore) DeleteByID(ctx context.Context, id int64) error { + return m.APITokenRepo.DeleteByID(ctx, id) +} + +// TouchLastUsed implements APITokenRepo. +func (m *MockStore) TouchLastUsed(ctx context.Context, id int64, lastUsedAt time.Time) error { + return m.APITokenRepo.TouchLastUsed(ctx, id, lastUsedAt) +} + // CreateSet implements SetRepo. func (m *MockStore) CreateSet(ctx context.Context, set *model.Set) (int64, error) { return m.SetRepo.CreateSet(ctx, set) @@ -409,6 +436,55 @@ func (m *MockUserRepo) CountUsers(ctx context.Context) (int, error) { return 0, nil } +// MockAPITokenRepo is a fake APITokenRepo. +type MockAPITokenRepo struct { + CreateFunc func(ctx context.Context, token *model.APIToken) (int64, error) + GetByHashFunc func(ctx context.Context, tokenHash string) (*model.APIToken, error) + ListByUserFunc func(ctx context.Context, userID int64) ([]model.APIToken, error) + DeleteByIDFunc func(ctx context.Context, id int64) error + TouchLastUsedFunc func(ctx context.Context, id int64, lastUsedAt time.Time) error +} + +// Create calls CreateFunc or returns a default ID. +func (m *MockAPITokenRepo) Create(ctx context.Context, token *model.APIToken) (int64, error) { + if m.CreateFunc != nil { + return m.CreateFunc(ctx, token) + } + return 1, nil +} + +// GetByHash calls GetByHashFunc or returns nil. +func (m *MockAPITokenRepo) GetByHash(ctx context.Context, tokenHash string) (*model.APIToken, error) { + if m.GetByHashFunc != nil { + return m.GetByHashFunc(ctx, tokenHash) + } + return nil, nil +} + +// ListByUser calls ListByUserFunc or returns nil. +func (m *MockAPITokenRepo) ListByUser(ctx context.Context, userID int64) ([]model.APIToken, error) { + if m.ListByUserFunc != nil { + return m.ListByUserFunc(ctx, userID) + } + return nil, nil +} + +// DeleteByID calls DeleteByIDFunc or returns nil. +func (m *MockAPITokenRepo) DeleteByID(ctx context.Context, id int64) error { + if m.DeleteByIDFunc != nil { + return m.DeleteByIDFunc(ctx, id) + } + return nil +} + +// TouchLastUsed calls TouchLastUsedFunc or returns nil. +func (m *MockAPITokenRepo) TouchLastUsed(ctx context.Context, id int64, lastUsedAt time.Time) error { + if m.TouchLastUsedFunc != nil { + return m.TouchLastUsedFunc(ctx, id, lastUsedAt) + } + return nil +} + // MockSetRepo is a fake SetRepo. type MockSetRepo struct { CreateSetFunc func(ctx context.Context, set *model.Set) (int64, error) diff --git a/player-server/internal/repository/repository.go b/player-server/internal/repository/repository.go index 4c4741e..c239b17 100644 --- a/player-server/internal/repository/repository.go +++ b/player-server/internal/repository/repository.go @@ -11,6 +11,7 @@ import ( // Store is the composite interface for all repositories. type Store interface { UserRepo + APITokenRepo SetRepo SetPermissionRepo MediaRepo @@ -153,6 +154,20 @@ type UserRepo interface { CountUsers(ctx context.Context) (int, error) } +// APITokenRepo manages API authentication tokens. +type APITokenRepo interface { + // Create stores a new API token and returns its database ID. + Create(ctx context.Context, token *model.APIToken) (int64, error) + // GetByHash returns an API token by token hash. + GetByHash(ctx context.Context, tokenHash string) (*model.APIToken, error) + // ListByUser returns all API tokens for a user. + ListByUser(ctx context.Context, userID int64) ([]model.APIToken, error) + // DeleteByID removes an API token by database ID. + DeleteByID(ctx context.Context, id int64) error + // TouchLastUsed updates an API token's last-used timestamp. + TouchLastUsed(ctx context.Context, id int64, lastUsedAt time.Time) error +} + // SetRepo manages media sets. type SetRepo interface { // CreateSet stores a new media set and returns its database ID. diff --git a/player-server/internal/repository/schema.go b/player-server/internal/repository/schema.go index a49dbb7..5d97a8b 100644 --- a/player-server/internal/repository/schema.go +++ b/player-server/internal/repository/schema.go @@ -16,6 +16,16 @@ CREATE TABLE IF NOT EXISTS users ( created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); +CREATE TABLE IF NOT EXISTS api_tokens ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + token_hash TEXT UNIQUE NOT NULL, + name TEXT NOT NULL, + last_used_at DATETIME, + expires_at DATETIME, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + CREATE TABLE IF NOT EXISTS sets ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, @@ -175,6 +185,8 @@ CREATE INDEX IF NOT EXISTS idx_media_rel_path ON media(set_id, rel_path); CREATE INDEX IF NOT EXISTS idx_media_deleted_at ON media(deleted_at); CREATE INDEX IF NOT EXISTS idx_media_type ON media(type); CREATE INDEX IF NOT EXISTS idx_media_filename ON media(file_name); +CREATE INDEX IF NOT EXISTS idx_api_tokens_token_hash ON api_tokens(token_hash); +CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id); CREATE INDEX IF NOT EXISTS idx_permissions_user ON set_permissions(user_id); CREATE INDEX IF NOT EXISTS idx_permissions_set ON set_permissions(set_id); CREATE INDEX IF NOT EXISTS idx_shares_expires ON shares(expires_at); @@ -224,6 +236,20 @@ type migration struct { } var migrations = []migration{ + { + name: "create_api_tokens", + sql: ` +CREATE TABLE IF NOT EXISTS api_tokens ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + token_hash TEXT UNIQUE NOT NULL, + name TEXT NOT NULL, + last_used_at DATETIME, + expires_at DATETIME, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); +`, + }, { name: "add_sets_is_podcast", sql: `ALTER TABLE sets ADD COLUMN is_podcast INTEGER NOT NULL DEFAULT 0;`, diff --git a/player-server/internal/repository/sqlite.go b/player-server/internal/repository/sqlite.go index 7409ce2..8d69ebf 100644 --- a/player-server/internal/repository/sqlite.go +++ b/player-server/internal/repository/sqlite.go @@ -59,6 +59,7 @@ func (s *SQLite) Ping(ctx context.Context) error { } var _ Store = (*SQLite)(nil) +var _ APITokenRepo = (*SQLite)(nil) var _ PodcastRepo = (*SQLite)(nil) type sqlScanner interface { diff --git a/player-server/internal/repository/sqlite_no_rows_test.go b/player-server/internal/repository/sqlite_no_rows_test.go index 48ac8a6..7da2811 100644 --- a/player-server/internal/repository/sqlite_no_rows_test.go +++ b/player-server/internal/repository/sqlite_no_rows_test.go @@ -30,6 +30,16 @@ func TestSQLite_NoRows_ReturnsNil(t *testing.T) { } }) + t.Run("GetByHash", func(t *testing.T) { + token, err := s.GetByHash(ctx, "missing") + if err != nil { + t.Fatalf("expected no error, got %v", err) + } + if token != nil { + t.Fatalf("expected nil, got %+v", token) + } + }) + t.Run("GetSetByID", func(t *testing.T) { st, err := s.GetSetByID(ctx, 9999) if err != nil { -- cgit v1.2.3