- commit
- 94d3e85
- parent
- 54a5a59
- author
- Eric Bower
- date
- 2024-03-25 19:43:50 +0000 UTC
feat: analytics for visitors on prose and pgs (#111)
11 files changed,
+488,
-36
M
Makefile
+2,
-1
1@@ -119,10 +119,11 @@ migrate:
2 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240120_add_payment_history.sql
3 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240221_add_project_acl.sql
4 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240311_add_public_key_name.sql
5+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240324_add_analytics_table.sql
6 .PHONY: migrate
7
8 latest:
9- $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240311_add_public_key_name.sql
10+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20240324_add_analytics_table.sql
11 .PHONY: latest
12
13 psql:
+56,
-0
1@@ -0,0 +1,56 @@
2+package main
3+
4+import (
5+ "log/slog"
6+ "os"
7+ "time"
8+
9+ "github.com/picosh/pico/db/postgres"
10+)
11+
12+func main() {
13+ logger := slog.Default()
14+ DbURL := os.Getenv("DATABASE_URL")
15+ dbpool := postgres.NewDB(DbURL, logger)
16+ now := time.Now()
17+
18+ stats, err := dbpool.VisitSummary(
19+ "5dabf12d-f0d7-44f1-9557-32a043ffac37",
20+ "user_id",
21+ "day",
22+ now.AddDate(0, 0, -now.Day()+1),
23+ )
24+ if err != nil {
25+ panic(err)
26+ }
27+
28+ for _, s := range stats.Intervals {
29+ logger.Info(
30+ "interval",
31+ "interval", s.Interval,
32+ "visitors", s.Visitors,
33+ "postID", s.PostID,
34+ "projectID", s.ProjectID,
35+ )
36+ }
37+
38+ for _, url := range stats.TopUrls {
39+ logger.Info(
40+ "url",
41+ "path", url.Url,
42+ "count", url.Count,
43+ "postID", url.PostID,
44+ "projectID", url.ProjectID,
45+ )
46+ }
47+
48+ for _, url := range stats.TopReferers {
49+ logger.Info(
50+ "referer",
51+ "path", url.Url,
52+ "count", url.Count,
53+ "postID", url.PostID,
54+ "projectID", url.ProjectID,
55+ )
56+ }
57+}
M
db/db.go
+35,
-1
1@@ -152,6 +152,39 @@ type PostAnalytics struct {
2 UpdateAt *time.Time
3 }
4
5+type AnalyticsVisits struct {
6+ ID string
7+ UserID string
8+ ProjectID string
9+ PostID string
10+ Host string
11+ Path string
12+ IpAddress string
13+ UserAgent string
14+ Referer string
15+ Status int
16+}
17+
18+type VisitInterval struct {
19+ PostID string
20+ ProjectID string
21+ Interval *time.Time
22+ Visitors int
23+}
24+
25+type VisitUrl struct {
26+ PostID string
27+ ProjectID string
28+ Url string
29+ Count int
30+}
31+
32+type SummaryVisits struct {
33+ Intervals []*VisitInterval
34+ TopUrls []*VisitUrl
35+ TopReferers []*VisitUrl
36+}
37+
38 type Pager struct {
39 Num int
40 Page int
41@@ -327,7 +360,8 @@ type DB interface {
42
43 ReplaceAliasesForPost(aliases []string, postID string) error
44
45- AddViewCount(postID string) (int, error)
46+ InsertVisit(view *AnalyticsVisits) error
47+ VisitSummary(fkID, by, interval string, origin time.Time) (*SummaryVisits, error)
48
49 AddPicoPlusUser(username string, paymentType, txId string) error
50 FindFeatureForUser(userID string, feature string) (*FeatureFlag, error)
+164,
-5
1@@ -998,13 +998,172 @@ func (me *PsqlDB) Close() error {
2 return me.Db.Close()
3 }
4
5-func (me *PsqlDB) AddViewCount(postID string) (int, error) {
6- views := 0
7- err := me.Db.QueryRow(sqlIncrementViews, postID).Scan(&views)
8+func newNullString(s string) sql.NullString {
9+ if len(s) == 0 {
10+ return sql.NullString{}
11+ }
12+ return sql.NullString{
13+ String: s,
14+ Valid: true,
15+ }
16+}
17+
18+func (me *PsqlDB) InsertVisit(view *db.AnalyticsVisits) error {
19+ _, err := me.Db.Exec(
20+ `INSERT INTO analytics_visits (user_id, project_id, post_id, host, path, ip_address, user_agent, referer, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);`,
21+ view.UserID,
22+ newNullString(view.ProjectID),
23+ newNullString(view.PostID),
24+ view.Host,
25+ view.Path,
26+ view.IpAddress,
27+ view.UserAgent,
28+ view.Referer,
29+ view.Status,
30+ )
31+ return err
32+}
33+
34+func (me *PsqlDB) visitUnique(fkID, by, interval string, origin time.Time) ([]*db.VisitInterval, error) {
35+ uniqueVisitors := fmt.Sprintf(`SELECT
36+ post_id,
37+ project_id,
38+ date_trunc('%s', created_at) as interval_start,
39+ count(DISTINCT ip_address) as unique_visitors
40+ FROM analytics_visits
41+ WHERE %s=$1 AND created_at >= $2
42+ GROUP BY post_id, project_id, interval_start`, interval, by)
43+
44+ intervals := []*db.VisitInterval{}
45+ rs, err := me.Db.Query(uniqueVisitors, fkID, origin)
46+ if err != nil {
47+ return nil, err
48+ }
49+
50+ for rs.Next() {
51+ interval := &db.VisitInterval{}
52+ var postID sql.NullString
53+ var projectID sql.NullString
54+ err := rs.Scan(
55+ &postID,
56+ &projectID,
57+ &interval.Interval,
58+ &interval.Visitors,
59+ )
60+ if err != nil {
61+ return nil, err
62+ }
63+ interval.PostID = postID.String
64+ interval.ProjectID = projectID.String
65+
66+ intervals = append(intervals, interval)
67+ }
68+ if rs.Err() != nil {
69+ return nil, rs.Err()
70+ }
71+ return intervals, nil
72+}
73+
74+func (me *PsqlDB) visitReferer(fkID, by string, origin time.Time) ([]*db.VisitUrl, error) {
75+ topUrls := fmt.Sprintf(`SELECT
76+ referer,
77+ post_id,
78+ project_id,
79+ count(*) as referer_count
80+ FROM analytics_visits
81+ WHERE %s=$1 AND created_at >= $2
82+ GROUP BY referer, post_id, project_id
83+ LIMIT 10`, by)
84+
85+ intervals := []*db.VisitUrl{}
86+ rs, err := me.Db.Query(topUrls, fkID, origin)
87+ if err != nil {
88+ return nil, err
89+ }
90+
91+ for rs.Next() {
92+ interval := &db.VisitUrl{}
93+ var postID sql.NullString
94+ var projectID sql.NullString
95+ err := rs.Scan(
96+ &interval.Url,
97+ &postID,
98+ &projectID,
99+ &interval.Count,
100+ )
101+ if err != nil {
102+ return nil, err
103+ }
104+ interval.PostID = postID.String
105+ interval.ProjectID = projectID.String
106+
107+ intervals = append(intervals, interval)
108+ }
109+ if rs.Err() != nil {
110+ return nil, rs.Err()
111+ }
112+ return intervals, nil
113+}
114+
115+func (me *PsqlDB) visitUrl(fkID, by string, origin time.Time) ([]*db.VisitUrl, error) {
116+ topUrls := fmt.Sprintf(`SELECT
117+ path,
118+ post_id,
119+ project_id,
120+ count(*) as path_count
121+ FROM analytics_visits
122+ WHERE %s=$1 AND created_at >= $2
123+ GROUP BY path, post_id, project_id
124+ LIMIT 10`, by)
125+
126+ intervals := []*db.VisitUrl{}
127+ rs, err := me.Db.Query(topUrls, fkID, origin)
128 if err != nil {
129- return views, err
130+ return nil, err
131+ }
132+
133+ for rs.Next() {
134+ interval := &db.VisitUrl{}
135+ var postID sql.NullString
136+ var projectID sql.NullString
137+ err := rs.Scan(
138+ &interval.Url,
139+ &postID,
140+ &projectID,
141+ &interval.Count,
142+ )
143+ if err != nil {
144+ return nil, err
145+ }
146+ interval.PostID = postID.String
147+ interval.ProjectID = projectID.String
148+
149+ intervals = append(intervals, interval)
150+ }
151+ if rs.Err() != nil {
152+ return nil, rs.Err()
153+ }
154+ return intervals, nil
155+}
156+
157+func (me *PsqlDB) VisitSummary(fkID, by, interval string, origin time.Time) (*db.SummaryVisits, error) {
158+ visitors, err := me.visitUnique(fkID, by, interval, origin)
159+ if err != nil {
160+ return nil, err
161+ }
162+ urls, err := me.visitUrl(fkID, by, origin)
163+ if err != nil {
164+ return nil, err
165+ }
166+ refs, err := me.visitReferer(fkID, by, origin)
167+ if err != nil {
168+ return nil, err
169 }
170- return views, nil
171+ return &db.SummaryVisits{
172+ Intervals: visitors,
173+ TopUrls: urls,
174+ TopReferers: refs,
175+ }, nil
176 }
177
178 func (me *PsqlDB) FindUsers() ([]*db.User, error) {
M
go.mod
+2,
-0
1@@ -32,7 +32,9 @@ require (
2 github.com/picosh/ptun v0.0.0-20240313192814-d0ca401937fe
3 github.com/picosh/send v0.0.0-20240217194807-77b972121e63
4 github.com/sendgrid/sendgrid-go v3.13.0+incompatible
5+ github.com/simplesurance/go-ip-anonymizer v0.0.0-20200429124537-35a880f8e87d
6 github.com/stripe/stripe-go/v75 v75.11.0
7+ github.com/x-way/crawlerdetect v0.2.20
8 github.com/yuin/goldmark v1.6.0
9 github.com/yuin/goldmark-highlighting v0.0.0-20220208100518-594be1970594
10 github.com/yuin/goldmark-meta v1.1.0
M
go.sum
+4,
-0
1@@ -234,6 +234,8 @@ github.com/shoenig/go-m1cpu v0.1.6 h1:nxdKQNcEB6vzgA2E2bvzKIYRuNj7XNJ4S/aRSwKzFt
2 github.com/shoenig/go-m1cpu v0.1.6/go.mod h1:1JJMcUBvfNwpq05QDQVAnx3gUHr9IYF7GNg9SUEw2VQ=
3 github.com/shoenig/test v0.6.4 h1:kVTaSd7WLz5WZ2IaoM0RSzRsUD+m8wRR+5qvntpn4LU=
4 github.com/shoenig/test v0.6.4/go.mod h1:byHiCGXqrVaflBLAMq/srcZIHynQPQgeyvkvXnjqq0k=
5+github.com/simplesurance/go-ip-anonymizer v0.0.0-20200429124537-35a880f8e87d h1:4FkGkGts6gLznca6fgclIvbupwbq543mb/fFkog4VIg=
6+github.com/simplesurance/go-ip-anonymizer v0.0.0-20200429124537-35a880f8e87d/go.mod h1:fTTj1EOmRdtuwYw3jF/1X2dTa0N1BdbZhrpA21N/S4I=
7 github.com/sirupsen/logrus v1.9.3 h1:dueUQJ1C2q9oE3F7wvmSGAaVtTmUizReu6fjN8uqzbQ=
8 github.com/sirupsen/logrus v1.9.3/go.mod h1:naHLuLoDiP4jHNo9R0sCBMtWGeIprob74mVsIT4qYEQ=
9 github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
10@@ -253,6 +255,8 @@ github.com/tklauser/go-sysconf v0.3.12 h1:0QaGUFOdQaIVdPgfITYzaTegZvdCjmYO52cSFA
11 github.com/tklauser/go-sysconf v0.3.12/go.mod h1:Ho14jnntGE1fpdOqQEEaiKRpvIavV0hSfmBq8nJbHYI=
12 github.com/tklauser/numcpus v0.6.1 h1:ng9scYS7az0Bk4OZLvrNXNSAO2Pxr1XXRAPyjhIx+Fk=
13 github.com/tklauser/numcpus v0.6.1/go.mod h1:1XfjsgE2zo8GVw7POkMbHENHzVg3GzmoZ9fESEdAacY=
14+github.com/x-way/crawlerdetect v0.2.20 h1:arg/9joh2/7Fgn0w+odNNWz0GF7iG7TzL4l7DNz5TUw=
15+github.com/x-way/crawlerdetect v0.2.20/go.mod h1:DVupfue81iupuoUmFjIyDUqPqGaJhtZfYQDWoP1ZUR4=
16 github.com/yuin/goldmark v1.4.5/go.mod h1:rmuwmfZ0+bvzB24eSC//bk1R1Zp3hM0OXYv/G2LIilg=
17 github.com/yuin/goldmark v1.4.13/go.mod h1:6yULJ656Px+3vBD8DxQVa3kxgyrAnzto9xy5taEt/CY=
18 github.com/yuin/goldmark v1.6.0 h1:boZcn2GTjpsynOsC0iJHnBWa4Bi0qzfJjthwauItG68=
+43,
-6
1@@ -1,6 +1,7 @@
2 package pgs
3
4 import (
5+ "errors"
6 "fmt"
7 "html/template"
8 "io"
9@@ -34,6 +35,7 @@ type AssetHandler struct {
10 UserID string
11 Bucket sst.Bucket
12 ImgProcessOpts *storage.ImgProcessOpts
13+ ProjectID string
14 }
15
16 func checkHandler(w http.ResponseWriter, r *http.Request) {
17@@ -216,6 +218,18 @@ func (h *AssetHandler) handle(w http.ResponseWriter, r *http.Request) {
18 "bucket", h.Bucket.Name,
19 "routes", strings.Join(attempts, ", "),
20 )
21+ // track 404s
22+ ch := shared.GetAnalyticsQueue(r)
23+ view, err := shared.AnalyticsVisitFromRequest(r, h.UserID)
24+ if err == nil {
25+ view.ProjectID = h.ProjectID
26+ view.Status = http.StatusNotFound
27+ ch <- view
28+ } else {
29+ if !errors.Is(err, shared.ErrAnalyticsDisabled) {
30+ h.Logger.Error("could not record analytics view", "err", err)
31+ }
32+ }
33 http.Error(w, "404 not found", http.StatusNotFound)
34 return
35 }
36@@ -259,6 +273,23 @@ func (h *AssetHandler) handle(w http.ResponseWriter, r *http.Request) {
37 w.Header().Set("content-type", contentType)
38 }
39
40+ finContentType := w.Header().Get("content-type")
41+
42+ // only track pages, not individual assets
43+ if finContentType == "text/html" {
44+ // track visit
45+ ch := shared.GetAnalyticsQueue(r)
46+ view, err := shared.AnalyticsVisitFromRequest(r, h.UserID)
47+ if err == nil {
48+ view.ProjectID = h.ProjectID
49+ ch <- view
50+ } else {
51+ if !errors.Is(err, shared.ErrAnalyticsDisabled) {
52+ h.Logger.Error("could not record analytics view", "err", err)
53+ }
54+ }
55+ }
56+
57 h.Logger.Info(
58 "serving asset",
59 "host", r.Host,
60@@ -266,7 +297,7 @@ func (h *AssetHandler) handle(w http.ResponseWriter, r *http.Request) {
61 "bucket", h.Bucket.Name,
62 "asset", assetFilepath,
63 "status", status,
64- "contentType", w.Header().Get("content-type"),
65+ "contentType", finContentType,
66 )
67
68 w.WriteHeader(status)
69@@ -317,6 +348,7 @@ func ServeAsset(fname string, opts *storage.ImgProcessOpts, fromImgs bool, hasPe
70 return
71 }
72
73+ projectID := ""
74 // TODO: this could probably be cleaned up more
75 // imgs wont have a project directory
76 projectDir := ""
77@@ -336,6 +368,7 @@ func ServeAsset(fname string, opts *storage.ImgProcessOpts, fromImgs bool, hasPe
78 return
79 }
80
81+ projectID = project.ID
82 projectDir = project.ProjectDir
83 if !hasPerm(project) {
84 http.Error(w, "You do not have access to this site", http.StatusUnauthorized)
85@@ -361,6 +394,7 @@ func ServeAsset(fname string, opts *storage.ImgProcessOpts, fromImgs bool, hasPe
86 Logger: logger,
87 Bucket: bucket,
88 ImgProcessOpts: opts,
89+ ProjectID: projectID,
90 }
91
92 asset.handle(w, r)
93@@ -425,8 +459,8 @@ func StartApiServer() {
94 cfg := NewConfigSite()
95 logger := cfg.Logger
96
97- db := postgres.NewDB(cfg.DbURL, cfg.Logger)
98- defer db.Close()
99+ dbpool := postgres.NewDB(cfg.DbURL, cfg.Logger)
100+ defer dbpool.Close()
101
102 var st storage.StorageServe
103 var err error
104@@ -441,10 +475,13 @@ func StartApiServer() {
105 return
106 }
107
108+ ch := make(chan *db.AnalyticsVisits)
109+ go shared.AnalyticsCollect(ch, dbpool, logger)
110 apiConfig := &shared.ApiConfig{
111- Cfg: cfg,
112- Dbpool: db,
113- Storage: st,
114+ Cfg: cfg,
115+ Dbpool: dbpool,
116+ Storage: st,
117+ AnalyticsQueue: ch,
118 }
119 handler := shared.CreateServe(mainRoutes, createSubdomainRoutes(publicPerm), apiConfig)
120 router := http.HandlerFunc(handler)
+29,
-14
1@@ -2,6 +2,7 @@ package prose
2
3 import (
4 "bytes"
5+ "errors"
6 "fmt"
7 "html/template"
8 "net/http"
9@@ -118,10 +119,6 @@ func GetBlogName(username string) string {
10 return fmt.Sprintf("%s's blog", username)
11 }
12
13-func isRequestTrackable(r *http.Request) bool {
14- return true
15-}
16-
17 func blogStyleHandler(w http.ResponseWriter, r *http.Request) {
18 username := shared.GetUsernameFromRequest(r)
19 dbpool := shared.GetDB(r)
20@@ -260,6 +257,17 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
21 postCollection = append(postCollection, p)
22 }
23
24+ // track visit
25+ ch := shared.GetAnalyticsQueue(r)
26+ view, err := shared.AnalyticsVisitFromRequest(r, user.ID)
27+ if err == nil {
28+ ch <- view
29+ } else {
30+ if !errors.Is(err, shared.ErrAnalyticsDisabled) {
31+ logger.Error("could not record analytics view", "err", err)
32+ }
33+ }
34+
35 data := BlogPageData{
36 Site: *cfg.GetSiteData(),
37 PageTitle: headerTxt.Title,
38@@ -324,6 +332,7 @@ func postHandler(w http.ResponseWriter, r *http.Request) {
39 username := shared.GetUsernameFromRequest(r)
40 subdomain := shared.GetSubdomain(r)
41 cfg := shared.GetCfg(r)
42+ ch := shared.GetAnalyticsQueue(r)
43
44 var slug string
45 if !cfg.IsSubdomains() || subdomain == "" {
46@@ -399,11 +408,14 @@ func postHandler(w http.ResponseWriter, r *http.Request) {
47 ogImageCard = parsedText.ImageCard
48 }
49
50- // validate and fire off analytic event
51- if isRequestTrackable(r) {
52- _, err := dbpool.AddViewCount(post.ID)
53- if err != nil {
54- logger.Error(err.Error())
55+ // track visit
56+ view, err := shared.AnalyticsVisitFromRequest(r, user.ID)
57+ if err == nil {
58+ view.PostID = post.ID
59+ ch <- view
60+ } else {
61+ if !errors.Is(err, shared.ErrAnalyticsDisabled) {
62+ logger.Error("could not record analytics view", "err", err)
63 }
64 }
65
66@@ -895,8 +907,8 @@ func createSubdomainRoutes(staticRoutes []shared.Route) []shared.Route {
67
68 func StartApiServer() {
69 cfg := NewConfigSite()
70- db := postgres.NewDB(cfg.ConfigCms.DbURL, cfg.Logger)
71- defer db.Close()
72+ dbpool := postgres.NewDB(cfg.ConfigCms.DbURL, cfg.Logger)
73+ defer dbpool.Close()
74 logger := cfg.Logger
75
76 var st storage.StorageServe
77@@ -920,10 +932,13 @@ func StartApiServer() {
78 mainRoutes := createMainRoutes(staticRoutes)
79 subdomainRoutes := createSubdomainRoutes(staticRoutes)
80
81+ ch := make(chan *db.AnalyticsVisits)
82+ go shared.AnalyticsCollect(ch, dbpool, logger)
83 apiConfig := &shared.ApiConfig{
84- Cfg: cfg,
85- Dbpool: db,
86- Storage: st,
87+ Cfg: cfg,
88+ Dbpool: dbpool,
89+ Storage: st,
90+ AnalyticsQueue: ch,
91 }
92 handler := shared.CreateServe(mainRoutes, subdomainRoutes, apiConfig)
93 router := http.HandlerFunc(handler)
1@@ -0,0 +1,108 @@
2+package shared
3+
4+import (
5+ "errors"
6+ "fmt"
7+ "log/slog"
8+ "net"
9+ "net/http"
10+ "net/url"
11+
12+ "github.com/picosh/pico/db"
13+ "github.com/simplesurance/go-ip-anonymizer/ipanonymizer"
14+ "github.com/x-way/crawlerdetect"
15+)
16+
17+func trackableRequest(r *http.Request) error {
18+ agent := r.UserAgent()
19+ // dont store requests from bots
20+ if crawlerdetect.IsCrawler(agent) {
21+ return fmt.Errorf(
22+ "request is likely from a bot (User-Agent: %s)",
23+ cleanUserAgent(agent),
24+ )
25+ }
26+ return nil
27+}
28+
29+func cleanIpAddress(ip string) (string, error) {
30+ host, _, err := net.SplitHostPort(ip)
31+ if err != nil {
32+ return "", err
33+ }
34+ // /16 IPv4 subnet mask
35+ // /64 IPv6 subnet mask
36+ anonymizer := ipanonymizer.NewWithMask(
37+ net.CIDRMask(16, 32),
38+ net.CIDRMask(64, 128),
39+ )
40+ anonIp, err := anonymizer.IPString(host)
41+ return anonIp, err
42+}
43+
44+func cleanUrl(curl *url.URL) (string, string) {
45+ // we don't want query params in the url for security reasons
46+ return curl.Host, curl.Path
47+}
48+
49+func cleanUserAgent(ua string) string {
50+ // truncate user-agent because http headers have no text limit
51+ if len(ua) > 1000 {
52+ return ua[:1000]
53+ }
54+ return ua
55+}
56+
57+func cleanReferer(ref string) (string, error) {
58+ // we only want to store host for security reasons
59+ // https://developer.mozilla.org/en-US/docs/Web/Security/Referer_header:_privacy_and_security_concerns
60+ u, err := url.Parse(ref)
61+ if err != nil {
62+ return "", err
63+ }
64+ return u.Host, nil
65+}
66+
67+var ErrAnalyticsDisabled = errors.New("owner does not have site analytics enabled")
68+
69+func AnalyticsVisitFromRequest(r *http.Request, userID string) (*db.AnalyticsVisits, error) {
70+ dbpool := GetDB(r)
71+ if !dbpool.HasFeatureForUser(userID, "analytics") {
72+ return nil, ErrAnalyticsDisabled
73+ }
74+
75+ err := trackableRequest(r)
76+ if err != nil {
77+ return nil, err
78+ }
79+
80+ ipAddress, err := cleanIpAddress(r.RemoteAddr)
81+ if err != nil {
82+ return nil, err
83+ }
84+ host, path := cleanUrl(r.URL)
85+
86+ referer, err := cleanReferer(r.Referer())
87+ if err != nil {
88+ return nil, err
89+ }
90+
91+ return &db.AnalyticsVisits{
92+ UserID: userID,
93+ Host: host,
94+ Path: path,
95+ IpAddress: ipAddress,
96+ UserAgent: cleanUserAgent(r.UserAgent()),
97+ Referer: referer,
98+ Status: http.StatusOK,
99+ }, nil
100+}
101+
102+func AnalyticsCollect(ch chan *db.AnalyticsVisits, dbpool db.DB, logger *slog.Logger) {
103+ for view := range ch {
104+ err := dbpool.InsertVisit(view)
105+ if err != nil {
106+ logger.Error("could not insert view record", "err", err)
107+ }
108+ }
109+}
1@@ -56,18 +56,20 @@ func CreatePProfRoutes(routes []Route) []Route {
2
3 type ServeFn func(http.ResponseWriter, *http.Request)
4 type ApiConfig struct {
5- Cfg *ConfigSite
6- Dbpool db.DB
7- Storage storage.StorageServe
8+ Cfg *ConfigSite
9+ Dbpool db.DB
10+ Storage storage.StorageServe
11+ AnalyticsQueue chan *db.AnalyticsVisits
12 }
13
14 func (hc *ApiConfig) CreateCtx(prevCtx context.Context, subdomain string) context.Context {
15- loggerCtx := context.WithValue(prevCtx, ctxLoggerKey{}, hc.Cfg.Logger)
16- subdomainCtx := context.WithValue(loggerCtx, ctxSubdomainKey{}, subdomain)
17- dbCtx := context.WithValue(subdomainCtx, ctxDBKey{}, hc.Dbpool)
18- storageCtx := context.WithValue(dbCtx, ctxStorageKey{}, hc.Storage)
19- cfgCtx := context.WithValue(storageCtx, ctxCfg{}, hc.Cfg)
20- return cfgCtx
21+ ctx := context.WithValue(prevCtx, ctxLoggerKey{}, hc.Cfg.Logger)
22+ ctx = context.WithValue(ctx, ctxSubdomainKey{}, subdomain)
23+ ctx = context.WithValue(ctx, ctxDBKey{}, hc.Dbpool)
24+ ctx = context.WithValue(ctx, ctxStorageKey{}, hc.Storage)
25+ ctx = context.WithValue(ctx, ctxCfg{}, hc.Cfg)
26+ ctx = context.WithValue(ctx, ctxAnalyticsQueue{}, hc.AnalyticsQueue)
27+ return ctx
28 }
29
30 func CreateServeBasic(routes []Route, ctx context.Context) ServeFn {
31@@ -144,6 +146,7 @@ type ctxKey struct{}
32 type ctxLoggerKey struct{}
33 type ctxSubdomainKey struct{}
34 type ctxCfg struct{}
35+type ctxAnalyticsQueue struct{}
36
37 func GetCfg(r *http.Request) *ConfigSite {
38 return r.Context().Value(ctxCfg{}).(*ConfigSite)
39@@ -186,3 +189,7 @@ func GetCustomDomain(host string, space string) string {
40
41 return ""
42 }
43+
44+func GetAnalyticsQueue(r *http.Request) chan *db.AnalyticsVisits {
45+ return r.Context().Value(ctxAnalyticsQueue{}).(chan *db.AnalyticsVisits)
46+}
1@@ -0,0 +1,29 @@
2+CREATE TABLE IF NOT EXISTS analytics_visits (
3+ id uuid NOT NULL DEFAULT uuid_generate_v4(),
4+ user_id uuid NOT NULL,
5+ project_id uuid,
6+ post_id uuid,
7+ host varchar(253),
8+ path varchar(2048),
9+ ip_address varchar(46),
10+ user_agent varchar(1000),
11+ referer varchar(253),
12+ status int4,
13+ created_at timestamp without time zone NOT NULL DEFAULT NOW(),
14+ CONSTRAINT analytics_visits_pkey PRIMARY KEY (id),
15+ CONSTRAINT fk_visits_user
16+ FOREIGN KEY(user_id)
17+ REFERENCES app_users(id)
18+ ON DELETE CASCADE
19+ ON UPDATE CASCADE,
20+ CONSTRAINT fk_visits_project
21+ FOREIGN KEY(project_id)
22+ REFERENCES projects(id)
23+ ON DELETE CASCADE
24+ ON UPDATE CASCADE,
25+ CONSTRAINT fk_visits_post
26+ FOREIGN KEY(post_id)
27+ REFERENCES posts(id)
28+ ON DELETE CASCADE
29+ ON UPDATE CASCADE
30+);