1 files changed, 66 insertions, 0 deletions
diff --git a/internal/storage/db.go b/internal/storage/db.go
index f07ddfe..51121e1 100644
--- a/internal/storage/db.go
+++ b/internal/storage/db.go
@@ -87,6 +87,13 @@ func (s *DB) migrate() error {
 		`ALTER TABLE executions ADD COLUMN commits_json TEXT NOT NULL DEFAULT '[]'`,
 		`ALTER TABLE tasks ADD COLUMN elaboration_input TEXT`,
 		`ALTER TABLE tasks ADD COLUMN project TEXT`,
+		`CREATE TABLE IF NOT EXISTS push_subscriptions (
+			id TEXT PRIMARY KEY,
+			endpoint TEXT NOT NULL UNIQUE,
+			p256dh_key TEXT NOT NULL,
+			auth_key TEXT NOT NULL,
+			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+		)`,
 	}
 	for _, m := range migrations {
 		if _, err := s.db.Exec(m); err != nil {
@@ -769,3 +776,62 @@ func (s *DB) UpdateExecutionChangestats(execID string, stats *task.Changestats)
 func scanExecutionRows(rows *sql.Rows) (*Execution, error) {
 	return scanExecution(rows)
 }
+
+// PushSubscription represents a browser push subscription.
+type PushSubscription struct {
+	ID        string    `json:"id"`
+	Endpoint  string    `json:"endpoint"`
+	P256DHKey string    `json:"p256dh_key"`
+	AuthKey   string    `json:"auth_key"`
+	CreatedAt time.Time `json:"created_at"`
+}
+
+// SavePushSubscription inserts or replaces a push subscription by endpoint.
+func (s *DB) SavePushSubscription(sub PushSubscription) error {
+	_, err := s.db.Exec(`
+		INSERT INTO push_subscriptions (id, endpoint, p256dh_key, auth_key)
+		VALUES (?, ?, ?, ?)
+		ON CONFLICT(endpoint) DO UPDATE SET
+			id         = excluded.id,
+			p256dh_key = excluded.p256dh_key,
+			auth_key   = excluded.auth_key`,
+		sub.ID, sub.Endpoint, sub.P256DHKey, sub.AuthKey,
+	)
+	return err
+}
+
+// DeletePushSubscription removes the subscription with the given endpoint.
+func (s *DB) DeletePushSubscription(endpoint string) error {
+	_, err := s.db.Exec(`DELETE FROM push_subscriptions WHERE endpoint = ?`, endpoint)
+	return err
+}
+
+// ListPushSubscriptions returns all registered push subscriptions.
+func (s *DB) ListPushSubscriptions() ([]PushSubscription, error) {
+	rows, err := s.db.Query(`SELECT id, endpoint, p256dh_key, auth_key, created_at FROM push_subscriptions ORDER BY created_at`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	var subs []PushSubscription
+	for rows.Next() {
+		var sub PushSubscription
+		var createdAt string
+		if err := rows.Scan(&sub.ID, &sub.Endpoint, &sub.P256DHKey, &sub.AuthKey, &createdAt); err != nil {
+			return nil, err
+		}
+		// Parse created_at; ignore errors (use zero time on failure).
+		for _, layout := range []string{time.RFC3339, "2006-01-02 15:04:05", "2006-01-02T15:04:05Z"} {
+			if t, err := time.Parse(layout, createdAt); err == nil {
+				sub.CreatedAt = t
+				break
+			}
+		}
+		subs = append(subs, sub)
+	}
+	if subs == nil {
+		subs = []PushSubscription{}
+	}
+	return subs, rows.Err()
+}