Add api_tokens table, model, and repository

9 files changed, 386 insertions, 0 deletions

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);
@@ -225,6 +237,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 {