refactor: tuns event logs

Logs

erock created pr with ps-113 on 2025-03-21T01:06:55Z

Patchsets

ps-113 by erock on 2025-03-21T01:06:55Z

Patchset `ps-113`

refactor: tuns event logs

Eric Bower

2025-03-21T01:06:36Z

Makefile

+2 -1

pkg/db/db.go

+1 -1

pkg/db/postgres/storage.go

+8 -8

pkg/shared/feed.go

+3 -4

sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql

+3 -0

refactor: tuns event logs

Makefile link

+2 -1

 1diff --git a/Makefile b/Makefile
 2index d452d2f..864fa34 100644
 3--- a/Makefile
 4+++ b/Makefile
 5@@ -127,10 +127,11 @@ migrate:
 6 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241125_add_content_type_to_analytics.sql
 7 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241202_add_more_idx_analytics.sql
 8 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
 9+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
10 .PHONY: migrate
11 
12 latest:
13-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
14+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
15 .PHONY: latest
16 
17 psql:

pkg/db/db.go link

+1 -1

 1diff --git a/pkg/db/db.go b/pkg/db/db.go
 2index eb5ca15..34099c8 100644
 3--- a/pkg/db/db.go
 4+++ b/pkg/db/db.go
 5@@ -333,8 +333,8 @@ type TunsEventLog struct {
 6 	EventType      string     `json:"event_type"`
 7 	TunnelType     string     `json:"tunnel_type"`
 8 	ConnectionType string     `json:"connection_type"`
 9-	TunnelAddrs    []string   `json:"tunnel_addrs"`
10 	CreatedAt      *time.Time `json:"created_at"`
11+	ConnectionID   string     `json:"connection_id"`
12 }
13 
14 var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")

pkg/db/postgres/storage.go link

+8 -8

 1diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
 2index 4b2c307..6d1fe59 100644
 3--- a/pkg/db/postgres/storage.go
 4+++ b/pkg/db/postgres/storage.go
 5@@ -12,7 +12,7 @@ import (
 6 
 7 	"slices"
 8 
 9-	"github.com/lib/pq"
10+	_ "github.com/lib/pq"
11 	"github.com/picosh/pico/pkg/db"
12 	"github.com/picosh/utils"
13 )
14@@ -1799,11 +1799,11 @@ func (me *PsqlDB) findPagesStats(userID string) (*db.UserServiceStats, error) {
15 func (me *PsqlDB) InsertTunsEventLog(log *db.TunsEventLog) error {
16 	_, err := me.Db.Exec(
17 		`INSERT INTO tuns_event_logs
18-			(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs)
19+			(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id)
20 		VALUES
21 			($1, $2, $3, $4, $5, $6, $7)`,
22 		log.UserId, log.ServerID, log.RemoteAddr, log.EventType, log.TunnelType,
23-		log.ConnectionType, pq.Array(log.TunnelAddrs),
24+		log.ConnectionType, log.ConnectionID,
25 	)
26 	return err
27 }
28@@ -1812,8 +1812,8 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
29 	logs := []*db.TunsEventLog{}
30 	fmt.Println(addr)
31 	rs, err := me.Db.Query(
32-		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
33-		FROM tuns_event_logs WHERE user_id=$1 AND tunnel_addrs @> ARRAY[$2] ORDER BY created_at DESC`, userID, addr)
34+		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
35+		FROM tuns_event_logs WHERE user_id=$1 AND connection_id=$2 ORDER BY created_at DESC`, userID, addr)
36 	if err != nil {
37 		return nil, err
38 	}
39@@ -1823,7 +1823,7 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
40 		err := rs.Scan(
41 			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
42 			&log.EventType, &log.TunnelType, &log.ConnectionType,
43-			(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
44+			&log.ConnectionID, &log.CreatedAt,
45 		)
46 		if err != nil {
47 			return nil, err
48@@ -1841,7 +1841,7 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
49 func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
50 	logs := []*db.TunsEventLog{}
51 	rs, err := me.Db.Query(
52-		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
53+		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
54 		FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
55 	if err != nil {
56 		return nil, err
57@@ -1852,7 +1852,7 @@ func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
58 		err := rs.Scan(
59 			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
60 			&log.EventType, &log.TunnelType, &log.ConnectionType,
61-			(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
62+			&log.ConnectionID, &log.CreatedAt,
63 		)
64 		if err != nil {
65 			return nil, err

pkg/shared/feed.go link

+3 -4

 1diff --git a/pkg/shared/feed.go b/pkg/shared/feed.go
 2index 63e71a5..8ee35f2 100644
 3--- a/pkg/shared/feed.go
 4+++ b/pkg/shared/feed.go
 5@@ -3,7 +3,6 @@ package shared
 6 import (
 7 	"fmt"
 8 	"sort"
 9-	"strings"
10 	"time"
11 
12 	"github.com/gorilla/feeds"
13@@ -134,19 +133,19 @@ func UserFeed(me db.DB, user *db.User, token string) (*feeds.Feed, error) {
14 	for _, eventLog := range tunsLogs {
15 		content := fmt.Sprintf(`Created At: %s<br />
16 Event type: %s<br />
17+Connection ID: %s<br />
18 Connection type: %s<br />
19 Remote addr: %s<br />
20 Tunnel type: %s<br />
21-Tunnel addrs: %s<br />
22 Server: %s`,
23 			eventLog.CreatedAt.Format(time.RFC3339), eventLog.EventType, eventLog.ConnectionType,
24-			eventLog.RemoteAddr, eventLog.TunnelType, eventLog.TunnelAddrs, eventLog.ServerID,
25+			eventLog.RemoteAddr, eventLog.TunnelType, eventLog.ConnectionID, eventLog.ServerID,
26 		)
27 		logItem := &feeds.Item{
28 			Id: fmt.Sprintf("%d", eventLog.CreatedAt.Unix()),
29 			Title: fmt.Sprintf(
30 				"%s tuns event for %s",
31-				eventLog.EventType, strings.Join(eventLog.TunnelAddrs, ", "),
32+				eventLog.EventType, eventLog.ConnectionID,
33 			),
34 			Link:        &feeds.Link{Href: "https://pico.sh"},
35 			Content:     content,

sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql link

+3 -0

1diff --git a/sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql b/sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
2new file mode 100644
3index 0000000..12f9b17
4--- /dev/null
5+++ b/sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
6@@ -0,0 +1,3 @@
7+DELETE FROM tuns_event_logs;
8+ALTER TABLE tuns_event_logs ADD COLUMN connection_id text NOT NULL;
9+ALTER TABLE tuns_event_logs DROP COLUMN tunnel_addrs;

Logs

Patchsets

Patchset ps-113

refactor: tuns event logs

refactor: tuns event logs

Patchset `ps-113`