Extend device_devices table (#1471)

* Add last_used_ts and IP to database

* Add migrations

* Rename column
Prepare statements

* Add interface method and implement it

Signed-off-by: Till Faelligen <tfaelligen@gmail.com>

* Rename struct fields

* Add user_agent to database

* Add userAgent to registration calls

* Add missing "IF NOT EXISTS"

* use txn writer

* Add UserAgent to Device

Co-authored-by: Kegsay <kegan@matrix.org>

3 files changed, 52 insertions, 8 deletions

diff --git a/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql b/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql
new file mode 100644
index 00000000..4f5f2b17
--- /dev/null
+++ b/userapi/storage/devices/postgres/deltas/20201001204705_last_seen_ts_ip.sql
@@ -0,0 +1,13 @@
+-- +goose Up
+-- +goose StatementBegin
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS last_seen_ts BIGINT NOT NULL;
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS ip TEXT;
+ALTER TABLE device_devices ADD COLUMN IF NOT EXISTS user_agent TEXT;
+-- +goose StatementEnd
+
+-- +goose Down
+-- +goose StatementBegin
+ALTER TABLE device_devices DROP COLUMN last_seen_ts;
+ALTER TABLE device_devices DROP COLUMN ip;
+ALTER TABLE device_devices DROP COLUMN user_agent;
+-- +goose StatementEnd
diff --git a/userapi/storage/devices/postgres/devices_table.go b/userapi/storage/devices/postgres/devices_table.go
index c06af754..2a4d337c 100644
--- a/userapi/storage/devices/postgres/devices_table.go
+++ b/userapi/storage/devices/postgres/devices_table.go
@@ -51,8 +51,15 @@ CREATE TABLE IF NOT EXISTS device_devices (
     -- When this devices was first recognised on the network, as a unix timestamp (ms resolution).
     created_ts BIGINT NOT NULL,
     -- The display name, human friendlier than device_id and updatable
-    display_name TEXT
-    -- TODO: device keys, device display names, last used ts and IP address?, token restrictions (if 3rd-party OAuth app)
+    display_name TEXT,
+	-- The time the device was last used, as a unix timestamp (ms resolution).
+	last_seen_ts BIGINT NOT NULL,
+	-- The last seen IP address of this device
+	ip TEXT,
+	-- User agent of this device
+	user_agent TEXT
+                                          
+    -- TODO: device keys, device display names, token restrictions (if 3rd-party OAuth app)
 );
 
 -- Device IDs must be unique for a given user.
@@ -60,7 +67,7 @@ CREATE UNIQUE INDEX IF NOT EXISTS device_localpart_id_idx ON device_devices(loca
 `
 
 const insertDeviceSQL = "" +
-	"INSERT INTO device_devices(device_id, localpart, access_token, created_ts, display_name) VALUES ($1, $2, $3, $4, $5)" +
+	"INSERT INTO device_devices(device_id, localpart, access_token, created_ts, display_name, last_seen_ts, ip, user_agent) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)" +
 	" RETURNING session_id"
 
 const selectDeviceByTokenSQL = "" +
@@ -87,6 +94,9 @@ const deleteDevicesSQL = "" +
 const selectDevicesByIDSQL = "" +
 	"SELECT device_id, localpart, display_name FROM device_devices WHERE device_id = ANY($1)"
 
+const updateDeviceLastSeen = "" +
+	"UPDATE device_devices SET last_seen_ts = $1, ip = $2 WHERE device_id = $3"
+
 type devicesStatements struct {
 	insertDeviceStmt             *sql.Stmt
 	selectDeviceByTokenStmt      *sql.Stmt
@@ -94,6 +104,7 @@ type devicesStatements struct {
 	selectDevicesByLocalpartStmt *sql.Stmt
 	selectDevicesByIDStmt        *sql.Stmt
 	updateDeviceNameStmt         *sql.Stmt
+	updateDeviceLastSeenStmt     *sql.Stmt
 	deleteDeviceStmt             *sql.Stmt
 	deleteDevicesByLocalpartStmt *sql.Stmt
 	deleteDevicesStmt            *sql.Stmt
@@ -132,6 +143,9 @@ func (s *devicesStatements) prepare(db *sql.DB, server gomatrixserverlib.ServerN
 	if s.selectDevicesByIDStmt, err = db.Prepare(selectDevicesByIDSQL); err != nil {
 		return
 	}
+	if s.updateDeviceLastSeenStmt, err = db.Prepare(updateDeviceLastSeen); err != nil {
+		return
+	}
 	s.serverName = server
 	return
 }
@@ -141,12 +155,12 @@ func (s *devicesStatements) prepare(db *sql.DB, server gomatrixserverlib.ServerN
 // Returns the device on success.
 func (s *devicesStatements) insertDevice(
 	ctx context.Context, txn *sql.Tx, id, localpart, accessToken string,
-	displayName *string,
+	displayName *string, ipAddr, userAgent string,
 ) (*api.Device, error) {
 	createdTimeMS := time.Now().UnixNano() / 1000000
 	var sessionID int64
 	stmt := sqlutil.TxStmt(txn, s.insertDeviceStmt)
-	if err := stmt.QueryRowContext(ctx, id, localpart, accessToken, createdTimeMS, displayName).Scan(&sessionID); err != nil {
+	if err := stmt.QueryRowContext(ctx, id, localpart, accessToken, createdTimeMS, displayName, createdTimeMS, ipAddr, userAgent).Scan(&sessionID); err != nil {
 		return nil, err
 	}
 	return &api.Device{
@@ -154,6 +168,9 @@ func (s *devicesStatements) insertDevice(
 		UserID:      userutil.MakeUserID(localpart, s.serverName),
 		AccessToken: accessToken,
 		SessionID:   sessionID,
+		LastSeenTS:  createdTimeMS,
+		LastSeenIP:  ipAddr,
+		UserAgent:   userAgent,
 	}, nil
 }
 
@@ -280,3 +297,10 @@ func (s *devicesStatements) selectDevicesByLocalpart(
 
 	return devices, rows.Err()
 }
+
+func (s *devicesStatements) updateDeviceLastSeen(ctx context.Context, txn *sql.Tx, deviceID, ipAddr string) error {
+	lastSeenTs := time.Now().UnixNano() / 1000000
+	stmt := sqlutil.TxStmt(txn, s.updateDeviceLastSeenStmt)
+	_, err := stmt.ExecContext(ctx, lastSeenTs, ipAddr, deviceID)
+	return err
+}
diff --git a/userapi/storage/devices/postgres/storage.go b/userapi/storage/devices/postgres/storage.go
index c5bd5b6c..faa5796b 100644
--- a/userapi/storage/devices/postgres/storage.go
+++ b/userapi/storage/devices/postgres/storage.go
@@ -83,7 +83,7 @@ func (d *Database) GetDevicesByID(ctx context.Context, deviceIDs []string) ([]ap
 // Returns the device on success.
 func (d *Database) CreateDevice(
 	ctx context.Context, localpart string, deviceID *string, accessToken string,
-	displayName *string,
+	displayName *string, ipAddr, userAgent string,
 ) (dev *api.Device, returnErr error) {
 	if deviceID != nil {
 		returnErr = sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
@@ -93,7 +93,7 @@ func (d *Database) CreateDevice(
 				return err
 			}
 
-			dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName)
+			dev, err = d.devices.insertDevice(ctx, txn, *deviceID, localpart, accessToken, displayName, ipAddr, userAgent)
 			return err
 		})
 	} else {
@@ -108,7 +108,7 @@ func (d *Database) CreateDevice(
 
 			returnErr = sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
 				var err error
-				dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName)
+				dev, err = d.devices.insertDevice(ctx, txn, newDeviceID, localpart, accessToken, displayName, ipAddr, userAgent)
 				return err
 			})
 			if returnErr == nil {
@@ -189,3 +189,10 @@ func (d *Database) RemoveAllDevices(
 	})
 	return
 }
+
+// UpdateDeviceLastSeen updates a the last seen timestamp and the ip address
+func (d *Database) UpdateDeviceLastSeen(ctx context.Context, deviceID, ipAddr string) error {
+	return sqlutil.WithTransaction(d.db, func(txn *sql.Tx) error {
+		return d.devices.updateDeviceLastSeen(ctx, txn, deviceID, ipAddr)
+	})
+}