Eric Bower
·
2026-01-08
db.go
1package db
2
3import (
4 "database/sql"
5 "database/sql/driver"
6 "encoding/json"
7 "errors"
8 "fmt"
9 "regexp"
10 "time"
11)
12
13var ErrNameTaken = errors.New("username has already been claimed")
14var ErrNameDenied = errors.New("username is on the denylist")
15var ErrNameInvalid = errors.New("username has invalid characters in it")
16var ErrPublicKeyTaken = errors.New("public key is already associated with another user")
17
18// sqlite uses string to BLOB type and postgres uses []uint8 for JSONB.
19func tcast(value any) ([]byte, error) {
20 switch val := value.(type) {
21 // sqlite3 BLOB
22 case string:
23 return []byte(val), nil
24 // postgres JSONB: []uint8
25 default:
26 b, ok := val.([]byte)
27 if !ok {
28 return []byte{}, errors.New("type assertion to []byte failed")
29 }
30 return b, nil
31 }
32}
33
34type PublicKey struct {
35 ID string `json:"id" db:"id"`
36 UserID string `json:"user_id" db:"user_id"`
37 Name string `json:"name" db:"name"`
38 Key string `json:"public_key" db:"public_key"`
39 CreatedAt *time.Time `json:"created_at" db:"created_at"`
40}
41
42type User struct {
43 ID string `json:"id" db:"id"`
44 Name string `json:"name" db:"name"`
45 PublicKey *PublicKey `json:"public_key,omitempty" db:"public_key,omitempty"`
46 CreatedAt *time.Time `json:"created_at" db:"created_at"`
47}
48
49type PostData struct {
50 ImgPath string `json:"img_path"`
51 LastDigest *time.Time `json:"last_digest"`
52 Attempts int `json:"attempts"`
53}
54
55// Make the Attrs struct implement the driver.Valuer interface. This method
56// simply returns the JSON-encoded representation of the struct.
57func (p PostData) Value() (driver.Value, error) {
58 return json.Marshal(p)
59}
60
61// Make the Attrs struct implement the sql.Scanner interface. This method
62// simply decodes a JSON-encoded value into the struct fields.
63func (p *PostData) Scan(value any) error {
64 b, err := tcast(value)
65 if err != nil {
66 return err
67 }
68
69 return json.Unmarshal(b, &p)
70}
71
72type Project struct {
73 ID string `json:"id" db:"id"`
74 UserID string `json:"user_id" db:"user_id"`
75 Name string `json:"name" db:"name"`
76 ProjectDir string `json:"project_dir" db:"project_dir"`
77 Username string `json:"username" db:"username"`
78 Acl ProjectAcl `json:"acl" db:"acl"`
79 Blocked string `json:"blocked" db:"blocked"`
80 CreatedAt *time.Time `json:"created_at" db:"created_at"`
81 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
82}
83
84type ProjectAcl struct {
85 Type string `json:"type" db:"type"`
86 Data []string `json:"data" db:"data"`
87}
88
89// Make the Attrs struct implement the driver.Valuer interface. This method
90// simply returns the JSON-encoded representation of the struct.
91func (p ProjectAcl) Value() (driver.Value, error) {
92 return json.Marshal(p)
93}
94
95// Make the Attrs struct implement the sql.Scanner interface. This method
96// simply decodes a JSON-encoded value into the struct fields.
97func (p *ProjectAcl) Scan(value any) error {
98 b, err := tcast(value)
99 if err != nil {
100 return err
101 }
102 return json.Unmarshal(b, &p)
103}
104
105type FeedItemData struct {
106 Title string `json:"title"`
107 Description string `json:"description"`
108 Content string `json:"content"`
109 Link string `json:"link"`
110 PublishedAt *time.Time `json:"published_at"`
111}
112
113// Make the Attrs struct implement the driver.Valuer interface. This method
114// simply returns the JSON-encoded representation of the struct.
115func (p FeedItemData) Value() (driver.Value, error) {
116 return json.Marshal(p)
117}
118
119// Make the Attrs struct implement the sql.Scanner interface. This method
120// simply decodes a JSON-encoded value into the struct fields.
121func (p *FeedItemData) Scan(value any) error {
122 b, err := tcast(value)
123 if err != nil {
124 return err
125 }
126
127 return json.Unmarshal(b, &p)
128}
129
130type Post struct {
131 ID string `json:"id" db:"id"`
132 UserID string `json:"user_id" db:"user_id"`
133 Filename string `json:"filename" db:"filename"`
134 Slug string `json:"slug" db:"slug"`
135 Title string `json:"title" db:"title"`
136 Text string `json:"text" db:"text"`
137 Description string `json:"description" db:"description"`
138 CreatedAt *time.Time `json:"created_at" db:"created_at"`
139 PublishAt *time.Time `json:"publish_at" db:"publish_at"`
140 Username string `json:"username" db:"name"`
141 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
142 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
143 Hidden bool `json:"hidden" db:"hidden"`
144 Views int `json:"views" db:"views"`
145 Space string `json:"space" db:"cur_space"`
146 Shasum string `json:"shasum" db:"shasum"`
147 FileSize int `json:"file_size" db:"file_size"`
148 MimeType string `json:"mime_type" db:"mime_type"`
149 Data PostData `json:"data" db:"data"`
150 Tags []string `json:"tags" db:"-"`
151
152 // computed
153 IsVirtual bool `db:"-"`
154}
155
156type Paginate[T any] struct {
157 Data []T
158 Total int
159}
160
161type VisitInterval struct {
162 Interval *time.Time `json:"interval" db:"interval"`
163 Visitors int `json:"visitors" db:"visitors"`
164}
165
166type VisitUrl struct {
167 Url string `json:"url" db:"url"`
168 Count int `json:"count" db:"count"`
169}
170
171type SummaryOpts struct {
172 Interval string
173 Origin time.Time
174 Host string
175 Path string
176 UserID string
177 Limit int
178}
179
180type SummaryVisits struct {
181 Intervals []*VisitInterval `json:"intervals"`
182 TopUrls []*VisitUrl `json:"top_urls"`
183 NotFoundUrls []*VisitUrl `json:"not_found_urls"`
184 TopReferers []*VisitUrl `json:"top_referers"`
185}
186
187type PostAnalytics struct {
188 ID string `json:"id" db:"id"`
189 PostID string `json:"post_id" db:"post_id"`
190 Views int `json:"views" db:"views"`
191 UpdateAt *time.Time `json:"updated_at" db:"updated_at"`
192}
193
194type AnalyticsVisits struct {
195 ID string `json:"id" db:"id"`
196 UserID string `json:"user_id" db:"user_id"`
197 ProjectID string `json:"project_id" db:"project_id"`
198 PostID string `json:"post_id" db:"post_id"`
199 Namespace string `json:"namespace" db:"namespace"`
200 Host string `json:"host" db:"host"`
201 Path string `json:"path" db:"path"`
202 IpAddress string `json:"ip_address" db:"ip_address"`
203 UserAgent string `json:"user_agent" db:"user_agent"`
204 Referer string `json:"referer" db:"referer"`
205 Status int `json:"status" db:"status"`
206 ContentType string `json:"content_type" db:"content_type"`
207}
208
209type AccessLogData struct{}
210
211func (p *AccessLogData) Scan(value any) error {
212 b, err := tcast(value)
213 if err != nil {
214 return err
215 }
216
217 return json.Unmarshal(b, &p)
218}
219
220type AccessLog struct {
221 ID string `json:"id" db:"id"`
222 UserID string `json:"user_id" db:"user_id"`
223 Service string `json:"service" db:"service"`
224 Pubkey string `json:"pubkey" db:"pubkey"`
225 Identity string `json:"identity" db:"identity"`
226 Data AccessLogData `json:"data" db:"data"`
227 CreatedAt *time.Time `json:"created_at" db:"created_at"`
228}
229
230type Pager struct {
231 Num int
232 Page int
233}
234
235type FeedItem struct {
236 ID string `json:"id" db:"id"`
237 PostID string `json:"post_id" db:"post_id"`
238 GUID string `json:"guid" db:"guid"`
239 Data FeedItemData `json:"data" db:"data"`
240 CreatedAt *time.Time `json:"created_at" db:"created_at"`
241}
242
243type Token struct {
244 ID string `json:"id" db:"id"`
245 UserID string `json:"user_id" db:"user_id"`
246 Name string `json:"name" db:"name"`
247 Token string `json:"token" db:"token"`
248 CreatedAt *time.Time `json:"created_at" db:"created_at"`
249 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
250}
251
252type FeatureFlag struct {
253 ID string `json:"id" db:"id"`
254 UserID string `json:"user_id" db:"user_id"`
255 PaymentHistoryID sql.NullString `json:"payment_history_id" db:"payment_history_id"`
256 Name string `json:"name" db:"name"`
257 CreatedAt *time.Time `json:"created_at" db:"created_at"`
258 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
259 Data FeatureFlagData `json:"data" db:"data"`
260}
261
262func NewFeatureFlag(userID, name string, storageMax uint64, fileMax int64, specialFileMax int64) *FeatureFlag {
263 return &FeatureFlag{
264 UserID: userID,
265 Name: name,
266 Data: FeatureFlagData{
267 StorageMax: storageMax,
268 FileMax: fileMax,
269 SpecialFileMax: specialFileMax,
270 },
271 }
272}
273
274func (ff *FeatureFlag) FindStorageMax(defaultSize uint64) uint64 {
275 if ff.Data.StorageMax == 0 {
276 return defaultSize
277 }
278 return ff.Data.StorageMax
279}
280
281func (ff *FeatureFlag) FindFileMax(defaultSize int64) int64 {
282 if ff.Data.FileMax == 0 {
283 return defaultSize
284 }
285 return ff.Data.FileMax
286}
287
288func (ff *FeatureFlag) FindSpecialFileMax(defaultSize int64) int64 {
289 if ff.Data.SpecialFileMax == 0 {
290 return defaultSize
291 }
292 return ff.Data.SpecialFileMax
293}
294
295func (ff *FeatureFlag) IsValid() bool {
296 if ff.ExpiresAt.IsZero() {
297 return false
298 }
299 return ff.ExpiresAt.After(time.Now())
300}
301
302type FeatureFlagData struct {
303 StorageMax uint64 `json:"storage_max" db:"storage_max"`
304 FileMax int64 `json:"file_max" db:"file_max"`
305 SpecialFileMax int64 `json:"special_file_max" db:"special_file_max"`
306}
307
308// Make the Attrs struct implement the driver.Valuer interface. This method
309// simply returns the JSON-encoded representation of the struct.
310func (p FeatureFlagData) Value() (driver.Value, error) {
311 return json.Marshal(p)
312}
313
314// Make the Attrs struct implement the sql.Scanner interface. This method
315// simply decodes a JSON-encoded value into the struct fields.
316func (p *FeatureFlagData) Scan(value any) error {
317 b, err := tcast(value)
318 if err != nil {
319 return err
320 }
321
322 return json.Unmarshal(b, &p)
323}
324
325type PaymentHistoryData struct {
326 Notes string `json:"notes"`
327 TxID string `json:"tx_id"`
328}
329
330// Make the Attrs struct implement the driver.Valuer interface. This method
331// simply returns the JSON-encoded representation of the struct.
332func (p PaymentHistoryData) Value() (driver.Value, error) {
333 return json.Marshal(p)
334}
335
336// Make the Attrs struct implement the sql.Scanner interface. This method
337// simply decodes a JSON-encoded value into the struct fields.
338func (p *PaymentHistoryData) Scan(value any) error {
339 b, err := tcast(value)
340 if err != nil {
341 return err
342 }
343
344 return json.Unmarshal(b, &p)
345}
346
347type ErrMultiplePublicKeys struct{}
348
349func (m *ErrMultiplePublicKeys) Error() string {
350 return "there are multiple users with this public key, you must provide the username when using SSH: `ssh <user>@<domain>`\n"
351}
352
353type UserStats struct {
354 Prose UserServiceStats
355 Pastes UserServiceStats
356 Feeds UserServiceStats
357 Pages UserServiceStats
358}
359
360type UserServiceStats struct {
361 Service string
362 Num int
363 FirstCreatedAt time.Time
364 LastestCreatedAt time.Time
365 LatestUpdatedAt time.Time
366}
367
368type TunsEventLog struct {
369 ID string `json:"id" db:"id"`
370 ServerID string `json:"server_id" db:"server_id"`
371 Time *time.Time `json:"time" db:"time"`
372 User string `json:"user" db:"user"`
373 UserId string `json:"user_id" db:"user_id"`
374 RemoteAddr string `json:"remote_addr" db:"remote_addr"`
375 EventType string `json:"event_type" db:"event_type"`
376 TunnelID string `json:"tunnel_id" db:"tunnel_id"`
377 TunnelType string `json:"tunnel_type" db:"tunnel_type"`
378 ConnectionType string `json:"connection_type" db:"connection_type"`
379 CreatedAt *time.Time `json:"created_at" db:"created_at"`
380}
381
382type PipeMonitor struct {
383 ID string `json:"id" db:"id"`
384 UserId string `json:"user_id" db:"user_id"`
385 Topic string `json:"topic" db:"topic"`
386 WindowDur time.Duration `json:"window_dur" db:"window_dur"`
387 WindowEnd *time.Time `json:"window_end" db:"window_end"`
388 LastPing *time.Time `json:"last_ping" db:"last_ping"`
389 CreatedAt *time.Time `json:"created_at" db:"created_at"`
390 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
391}
392
393type PipeMonitorHistory struct {
394 ID string `json:"id" db:"id"`
395 MonitorID string `json:"monitor_id" db:"monitor_id"`
396 WindowDur time.Duration `json:"window_dur" db:"window_dur"`
397 WindowEnd *time.Time `json:"window_end" db:"window_end"`
398 LastPing *time.Time `json:"last_ping" db:"last_ping"`
399 CreatedAt *time.Time `json:"created_at" db:"created_at"`
400 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
401}
402
403type UptimeResult struct {
404 TotalDuration time.Duration
405 UptimeDuration time.Duration
406 UptimePercent float64
407}
408
409func ComputeUptime(history []*PipeMonitorHistory, from, to time.Time) UptimeResult {
410 totalDuration := to.Sub(from)
411 if totalDuration <= 0 {
412 return UptimeResult{}
413 }
414
415 if len(history) == 0 {
416 return UptimeResult{TotalDuration: totalDuration}
417 }
418
419 type interval struct {
420 start, end time.Time
421 }
422
423 var intervals []interval
424 for _, h := range history {
425 if h.WindowEnd == nil {
426 continue
427 }
428 windowStart := h.WindowEnd.Add(-h.WindowDur)
429 windowEnd := *h.WindowEnd
430
431 if windowStart.Before(from) {
432 windowStart = from
433 }
434 if windowEnd.After(to) {
435 windowEnd = to
436 }
437
438 if windowStart.Before(windowEnd) {
439 intervals = append(intervals, interval{start: windowStart, end: windowEnd})
440 }
441 }
442
443 if len(intervals) == 0 {
444 return UptimeResult{TotalDuration: totalDuration}
445 }
446
447 // Sort by start time
448 for i := range intervals {
449 for j := i + 1; j < len(intervals); j++ {
450 if intervals[j].start.Before(intervals[i].start) {
451 intervals[i], intervals[j] = intervals[j], intervals[i]
452 }
453 }
454 }
455
456 // Merge overlapping intervals
457 merged := []interval{intervals[0]}
458 for _, curr := range intervals[1:] {
459 last := &merged[len(merged)-1]
460 if !curr.start.After(last.end) {
461 if curr.end.After(last.end) {
462 last.end = curr.end
463 }
464 } else {
465 merged = append(merged, curr)
466 }
467 }
468
469 var uptimeDuration time.Duration
470 for _, iv := range merged {
471 uptimeDuration += iv.end.Sub(iv.start)
472 }
473
474 uptimePercent := float64(uptimeDuration) / float64(totalDuration) * 100
475
476 return UptimeResult{
477 TotalDuration: totalDuration,
478 UptimeDuration: uptimeDuration,
479 UptimePercent: uptimePercent,
480 }
481}
482
483func (m *PipeMonitor) Status() error {
484 if m.LastPing == nil {
485 return fmt.Errorf("no ping received yet")
486 }
487 if m.WindowEnd == nil {
488 return fmt.Errorf("window end not set")
489 }
490 now := time.Now().UTC()
491 if now.After(*m.WindowEnd) {
492 return fmt.Errorf(
493 "window expired at %s",
494 m.WindowEnd.UTC().Format("2006-01-02 15:04:05Z"),
495 )
496 }
497 windowStart := m.WindowEnd.Add(-m.WindowDur)
498 lastPingAfterStart := !m.LastPing.Before(windowStart)
499 if !lastPingAfterStart {
500 return fmt.Errorf(
501 "last ping before window start: %s",
502 windowStart.UTC().Format("2006-01-02 15:04:05Z"),
503 )
504 }
505 return nil
506}
507
508func (m *PipeMonitor) GetNextWindow() *time.Time {
509 win := m.WindowEnd.Add(m.WindowDur)
510 return &win
511}
512
513var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")
514var DenyList = []string{
515 "admin",
516 "abuse",
517 "cgi",
518 "ops",
519 "help",
520 "spec",
521 "root",
522 "new",
523 "create",
524 "www",
525 "public",
526 "global",
527 "g",
528 "root",
529 "localhost",
530 "ams",
531 "ash",
532 "nue",
533}
534
535type DB interface {
536 RegisterUser(name, pubkey, comment string) (*User, error)
537 UpdatePublicKey(pubkeyID, name string) (*PublicKey, error)
538 InsertPublicKey(userID, pubkey, name string) error
539 FindKeysByUser(user *User) ([]*PublicKey, error)
540 RemoveKeys(pubkeyIDs []string) error
541
542 FindUsers() ([]*User, error)
543 FindUserByName(name string) (*User, error)
544 FindUserByKey(name string, pubkey string) (*User, error)
545 FindUserByPubkey(pubkey string) (*User, error)
546 FindUser(userID string) (*User, error)
547
548 FindUserByToken(token string) (*User, error)
549 FindTokensByUser(userID string) ([]*Token, error)
550 InsertToken(userID, name string) (string, error)
551 UpsertToken(userID, name string) (string, error)
552 RemoveToken(tokenID string) error
553
554 FindPosts() ([]*Post, error)
555 FindPost(postID string) (*Post, error)
556 FindPostsByUser(pager *Pager, userID string, space string) (*Paginate[*Post], error)
557 FindAllPostsByUser(userID string, space string) ([]*Post, error)
558 FindUsersWithPost(space string) ([]*User, error)
559 FindExpiredPosts(space string) ([]*Post, error)
560 FindPostWithFilename(filename string, userID string, space string) (*Post, error)
561 FindPostWithSlug(slug string, userID string, space string) (*Post, error)
562 FindPostsByFeed(pager *Pager, space string) (*Paginate[*Post], error)
563 InsertPost(post *Post) (*Post, error)
564 UpdatePost(post *Post) (*Post, error)
565 RemovePosts(postIDs []string) error
566
567 ReplaceTagsByPost(tags []string, postID string) error
568 FindUserPostsByTag(pager *Pager, tag, userID, space string) (*Paginate[*Post], error)
569 FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
570 FindPopularTags(space string) ([]string, error)
571 ReplaceAliasesByPost(aliases []string, postID string) error
572
573 InsertVisit(view *AnalyticsVisits) error
574 VisitSummary(opts *SummaryOpts) (*SummaryVisits, error)
575 FindVisitSiteList(opts *SummaryOpts) ([]*VisitUrl, error)
576 VisitUrlNotFound(opts *SummaryOpts) ([]*VisitUrl, error)
577
578 AddPicoPlusUser(username, email, paymentType, txId string) error
579 FindFeature(userID string, feature string) (*FeatureFlag, error)
580 FindFeaturesByUser(userID string) ([]*FeatureFlag, error)
581 HasFeatureByUser(userID string, feature string) bool
582
583 InsertFeature(userID, name string, expiresAt time.Time) (*FeatureFlag, error)
584 RemoveFeature(userID, names string) error
585
586 InsertFeedItems(postID string, items []*FeedItem) error
587 FindFeedItemsByPostID(postID string) ([]*FeedItem, error)
588
589 UpsertProject(userID, name, projectDir string) (*Project, error)
590 FindProjectByName(userID, name string) (*Project, error)
591
592 FindUserStats(userID string) (*UserStats, error)
593
594 InsertTunsEventLog(log *TunsEventLog) error
595 FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
596 FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
597
598 InsertAccessLog(log *AccessLog) error
599 FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
600 FindPubkeysInAccessLogs(userID string) ([]string, error)
601 FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*AccessLog, error)
602
603 UpsertPipeMonitor(userID, topic string, dur time.Duration, winEnd *time.Time) error
604 UpdatePipeMonitorLastPing(userID, topic string, lastPing *time.Time) error
605 RemovePipeMonitor(userID, topic string) error
606 FindPipeMonitorByTopic(userID, topic string) (*PipeMonitor, error)
607 FindPipeMonitorsByUser(userID string) ([]*PipeMonitor, error)
608
609 InsertPipeMonitorHistory(monitorID string, windowDur time.Duration, windowEnd, lastPing *time.Time) error
610 FindPipeMonitorHistory(monitorID string, from, to time.Time) ([]*PipeMonitorHistory, error)
611
612 Close() error
613}