CUB-228: add battery_calibration_offset to cameras table

- Add column to 001_create_tables.sql for fresh databases
- Add migration 002 for existing databases (idempotent via
  pragma_table_info check)
- Implement runIncrementalMigrations in db.go
- Add BatteryCalibrationOffset to Camera model
- Update all camera SELECT queries (cameras List, detail, MQTT
  subscriber getCamera, register)

go.mod

@@ -12,12 +12,9 @@ require (
 require (
 	github.com/dustin/go-humanize v1.0.1 // indirect
 	github.com/google/uuid v1.6.0 // indirect
-	github.com/gorilla/websocket v1.5.3 // indirect
 	github.com/mattn/go-isatty v0.0.20 // indirect
 	github.com/ncruces/go-strftime v1.0.0 // indirect
 	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
-	golang.org/x/net v0.27.0 // indirect
-	golang.org/x/sync v0.20.0 // indirect
 	golang.org/x/sys v0.42.0 // indirect
 	modernc.org/libc v1.72.3 // indirect
 	modernc.org/mathutil v1.7.1 // indirect

go.sum

@@ -1,15 +1,11 @@
 github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
 github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
-github.com/eclipse/paho.mqtt.golang v1.5.0 h1:EH+bUVJNgttidWFkLLVKaQPGmkTUfQQqjOsyvMGvD6o=
-github.com/eclipse/paho.mqtt.golang v1.5.0/go.mod h1:du/2qNQVqJf/Sqs4MEL77kR8QTqANF7XU7Fk0aOTAgk=
 github.com/go-chi/chi/v5 v5.2.5 h1:Eg4myHZBjyvJmAFjFvWgrqDTXFyOzjj7YIm3L3mu6Ug=
 github.com/go-chi/chi/v5 v5.2.5/go.mod h1:X7Gx4mteadT3eDOMTsXzmI4/rwUpOwBHLpAfupzFJP0=
 github.com/google/pprof v0.0.0-20250317173921-a4b03ec1a45e h1:ijClszYn+mADRFY17kjQEVQ1XRhq2/JR1M3sGqeJoxs=
 github.com/google/pprof v0.0.0-20250317173921-a4b03ec1a45e/go.mod h1:boTsfXsheKC2y+lKOCMpSfarhxDeIzfZG1jqGcPl3cA=
 github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0=
 github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
-github.com/gorilla/websocket v1.5.3 h1:saDtZ6Pbx/0u+bgYQ3q96pZgCzfhKXGPqt7kZ72aNNg=
-github.com/gorilla/websocket v1.5.3/go.mod h1:YR8l580nyteQvAITg2hZ9XVh4b55+EU/adAjf1fMHhE=
 github.com/hashicorp/golang-lru/v2 v2.0.7 h1:a+bsQ5rvGLjzHuww6tVxozPZFVghXaHOwFs4luLUK2k=
 github.com/hashicorp/golang-lru/v2 v2.0.7/go.mod h1:QeFd9opnmA6QUJc5vARoKUSoFhyfM2/ZepoAG6RGpeM=
 github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWEY=
@@ -20,8 +16,6 @@ github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94
 github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
 golang.org/x/mod v0.33.0 h1:tHFzIWbBifEmbwtGz65eaWyGiGZatSrT9prnU8DbVL8=
 golang.org/x/mod v0.33.0/go.mod h1:swjeQEj+6r7fODbD2cqrnje9PnziFuw4bmLbBZFrQ5w=
-golang.org/x/net v0.27.0 h1:5K3Njcw06/l2y9vpGCSdcxWOYHOUk3dVNGDXN+FvAys=
-golang.org/x/net v0.27.0/go.mod h1:dDi0PyhWNoiUOrAS8uXv/vnScO4wnHQO4mj9fn/RytE=
 golang.org/x/sync v0.20.0 h1:e0PTpb7pjO8GAtTs2dQ6jYa5BWYlMuX047Dco/pItO4=
 golang.org/x/sync v0.20.0/go.mod h1:9xrNwdLfx4jkKbNva9FpL6vEN7evnE43NNNJQ2LF3+0=
 golang.org/x/sys v0.6.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=

internal/api/cameras.go

@@ -132,10 +132,10 @@ func GetCameraDetail(database *db.DB) http.HandlerFunc {
 		// Get camera info
 		var c models.Camera
 		err := database.QueryRowContext(r.Context(), `
-			SELECT camera_id, friendly_name, mac_address, created_at, updated_at
+			SELECT camera_id, friendly_name, mac_address, battery_calibration_offset, created_at, updated_at
 			FROM cameras WHERE camera_id = ?
 		`, cameraID).Scan(
-			&c.CameraID, &c.FriendlyName, &c.MacAddress,
+			&c.CameraID, &c.FriendlyName, &c.MacAddress, &c.BatteryCalibrationOffset,
 			&c.CreatedAt, &c.UpdatedAt,
 		)
 		if err == sql.ErrNoRows {

internal/db/db.go

@@ -14,6 +14,9 @@ import (
 //go:embed migrations/001_create_tables.sql
 var migration001 string
 
+//go:embed migrations/002_add_camera_calibration.sql
+var migration002 string
+
 // DB wraps the sql.DB with connection-level settings.
 type DB struct {
 	*sql.DB
@@ -62,6 +65,12 @@ func Open(path string) (*DB, error) {
 			return nil, err
 		}
 		log.Println("Migrations complete")
+	} else {
+		// Run incremental migrations on existing databases
+		if err := runIncrementalMigrations(db); err != nil {
+			db.Close()
+			return nil, err
+		}
 	}
 
 	return &DB{db}, nil
@@ -83,6 +92,25 @@ func migrate(db *sql.DB, sql string) error {
 	return nil
 }
 
+// runIncrementalMigrations applies migrations that haven't been run yet on
+// an existing database (one where the 001 schema already exists).
+func runIncrementalMigrations(db *sql.DB) error {
+	// Migration 002: add battery_calibration_offset if it doesn't exist
+	var colCount int
+	err := db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('cameras') WHERE name = 'battery_calibration_offset'`).Scan(&colCount)
+	if err != nil {
+		return err
+	}
+	if colCount == 0 {
+		log.Println("Running migration 002: add battery_calibration_offset")
+		if err := migrate(db, migration002); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
 // splitSQL splits a SQL string on semicolons, respecting quoted strings.
 func splitSQL(sql string) []string {
 	var stmts []string

internal/db/migrations/001_create_tables.sql

@@ -3,11 +3,12 @@
 
 -- Cameras table: registry of all GoPro cameras
 CREATE TABLE IF NOT EXISTS cameras (
-    camera_id     TEXT PRIMARY KEY,
+    camera_id                    TEXT PRIMARY KEY,
-    friendly_name TEXT NOT NULL,
+    friendly_name                TEXT NOT NULL,
-    mac_address   TEXT UNIQUE,
+    mac_address                  TEXT UNIQUE,
-    created_at    DATETIME NOT NULL DEFAULT (datetime('now')),
+    battery_calibration_offset   REAL,
-    updated_at    DATETIME NOT NULL DEFAULT (datetime('now'))
+    created_at                   DATETIME NOT NULL DEFAULT (datetime('now')),
+    updated_at                   DATETIME NOT NULL DEFAULT (datetime('now'))
 );
 
 CREATE INDEX IF NOT EXISTS idx_cameras_mac ON cameras(mac_address);

internal/db/migrations/002_add_camera_calibration.sql

@@ -0,0 +1,5 @@
+-- Migration 002: Add battery_calibration_offset to cameras table
+-- This column stores a per-camera calibration value for converting raw battery
+-- readings (e.g. GoPro Hero 3 byte at offset 57) into percentage values.
+
+ALTER TABLE cameras ADD COLUMN battery_calibration_offset REAL;

internal/mqtt/subscriber.go

@@ -404,9 +404,9 @@ func extractCameraID(topic string) string {
 func getCamera(db *db.DB, cameraID string) (models.Camera, error) {
 	var cam models.Camera
 	err := db.QueryRow(
-		"SELECT camera_id, friendly_name, COALESCE(mac_address,''), created_at, updated_at FROM cameras WHERE camera_id = ?",
+		"SELECT camera_id, friendly_name, COALESCE(mac_address,''), COALESCE(battery_calibration_offset, NULL), created_at, updated_at FROM cameras WHERE camera_id = ?",
 		cameraID,
-	).Scan(&cam.CameraID, &cam.FriendlyName, &cam.MacAddress, &cam.CreatedAt, &cam.UpdatedAt)
+	).Scan(&cam.CameraID, &cam.FriendlyName, &cam.MacAddress, &cam.BatteryCalibrationOffset, &cam.CreatedAt, &cam.UpdatedAt)
 	return cam, err
 }
 

pkg/models/camera.go

@@ -7,11 +7,12 @@ import (
 
 // Camera represents a registered GoPro camera in the system.
 type Camera struct {
-	CameraID      string    `json:"camera_id"`
+	CameraID                  string    `json:"camera_id"`
-	FriendlyName  string    `json:"friendly_name"`
+	FriendlyName              string    `json:"friendly_name"`
-	MacAddress    string    `json:"mac_address,omitempty"`
+	MacAddress                string    `json:"mac_address,omitempty"`
-	CreatedAt     time.Time `json:"created_at"`
+	BatteryCalibrationOffset  *float64  `json:"battery_calibration_offset,omitempty"`
-	UpdatedAt     time.Time `json:"updated_at"`
+	CreatedAt                 time.Time `json:"created_at"`
+	UpdatedAt                 time.Time `json:"updated_at"`
 }
 
 // StatusLog records a single status poll from an ESP8266 node.