Eric Bower
·
2026-05-03
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"` // public, pico, pubkeys, private, http-pass
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 MobileVisitors int `json:"mobile_visitors" db:"mobile_visitors"`
165 DesktopVisitors int `json:"desktop_visitors" db:"desktop_visitors"`
166}
167
168type VisitUrl struct {
169 Url string `json:"url" db:"url"`
170 Count int `json:"count" db:"count"`
171}
172
173type SummaryOpts struct {
174 Interval string
175 Origin time.Time
176 Host string
177 Path string
178 UserID string
179 Limit int
180}
181
182type SummaryVisits struct {
183 Intervals []*VisitInterval `json:"intervals"`
184 TopUrls []*VisitUrl `json:"top_urls"`
185 NotFoundUrls []*VisitUrl `json:"not_found_urls"`
186 TopReferers []*VisitUrl `json:"top_referers"`
187}
188
189type PostAnalytics struct {
190 ID string `json:"id" db:"id"`
191 PostID string `json:"post_id" db:"post_id"`
192 Views int `json:"views" db:"views"`
193 UpdateAt *time.Time `json:"updated_at" db:"updated_at"`
194}
195
196type AnalyticsVisits struct {
197 ID string `json:"id" db:"id"`
198 UserID string `json:"user_id" db:"user_id"`
199 ProjectID string `json:"project_id" db:"project_id"`
200 PostID string `json:"post_id" db:"post_id"`
201 Namespace string `json:"namespace" db:"namespace"`
202 Host string `json:"host" db:"host"`
203 Path string `json:"path" db:"path"`
204 IpAddress string `json:"ip_address" db:"ip_address"`
205 UserAgent string `json:"user_agent" db:"user_agent"`
206 Referer string `json:"referer" db:"referer"`
207 Status int `json:"status" db:"status"`
208 ContentType string `json:"content_type" db:"content_type"`
209}
210
211type AccessLogData struct{}
212
213func (p *AccessLogData) Scan(value any) error {
214 b, err := tcast(value)
215 if err != nil {
216 return err
217 }
218
219 return json.Unmarshal(b, &p)
220}
221
222type AccessLog struct {
223 ID string `json:"id" db:"id"`
224 UserID string `json:"user_id" db:"user_id"`
225 Service string `json:"service" db:"service"`
226 Pubkey string `json:"pubkey" db:"pubkey"`
227 Identity string `json:"identity" db:"identity"`
228 Data AccessLogData `json:"data" db:"data"`
229 CreatedAt *time.Time `json:"created_at" db:"created_at"`
230}
231
232type Pager struct {
233 Num int
234 Page int
235}
236
237type FeedItem struct {
238 ID string `json:"id" db:"id"`
239 PostID string `json:"post_id" db:"post_id"`
240 GUID string `json:"guid" db:"guid"`
241 Data FeedItemData `json:"data" db:"data"`
242 CreatedAt *time.Time `json:"created_at" db:"created_at"`
243}
244
245type Token struct {
246 ID string `json:"id" db:"id"`
247 UserID string `json:"user_id" db:"user_id"`
248 Name string `json:"name" db:"name"`
249 Token string `json:"token" db:"token"`
250 CreatedAt *time.Time `json:"created_at" db:"created_at"`
251 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
252}
253
254type FormEntry struct {
255 ID string `json:"id" db:"id"`
256 UserID string `json:"-" db:"user_id"`
257 Name string `json:"-" db:"name"`
258 Data FormEntryData `json:"data" db:"data"`
259 CreatedAt *time.Time `json:"created_at" db:"created_at"`
260}
261
262type FormEntryData map[string]interface{}
263
264// Make the FormEntry struct implement the driver.Valuer interface.
265func (f FormEntryData) Value() (driver.Value, error) {
266 return json.Marshal(f)
267}
268
269// Make the FormEntry struct implement the sql.Scanner interface.
270func (f *FormEntryData) Scan(value any) error {
271 b, err := tcast(value)
272 if err != nil {
273 return err
274 }
275 return json.Unmarshal(b, &f)
276}
277
278type FeatureFlag struct {
279 ID string `json:"id" db:"id"`
280 UserID string `json:"user_id" db:"user_id"`
281 PaymentHistoryID sql.NullString `json:"payment_history_id" db:"payment_history_id"`
282 Name string `json:"name" db:"name"`
283 CreatedAt *time.Time `json:"created_at" db:"created_at"`
284 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
285 Data FeatureFlagData `json:"data" db:"data"`
286}
287
288func NewFeatureFlag(userID, name string, storageMax uint64, fileMax int64, specialFileMax int64) *FeatureFlag {
289 return &FeatureFlag{
290 UserID: userID,
291 Name: name,
292 Data: FeatureFlagData{
293 StorageMax: storageMax,
294 FileMax: fileMax,
295 SpecialFileMax: specialFileMax,
296 },
297 }
298}
299
300func (ff *FeatureFlag) FindStorageMax(defaultSize uint64) uint64 {
301 if ff.Data.StorageMax == 0 {
302 return defaultSize
303 }
304 return ff.Data.StorageMax
305}
306
307func (ff *FeatureFlag) FindFileMax(defaultSize int64) int64 {
308 if ff.Data.FileMax == 0 {
309 return defaultSize
310 }
311 return ff.Data.FileMax
312}
313
314func (ff *FeatureFlag) FindSpecialFileMax(defaultSize int64) int64 {
315 if ff.Data.SpecialFileMax == 0 {
316 return defaultSize
317 }
318 return ff.Data.SpecialFileMax
319}
320
321func (ff *FeatureFlag) IsValid() bool {
322 if ff.ExpiresAt.IsZero() {
323 return false
324 }
325 return ff.ExpiresAt.After(time.Now())
326}
327
328type FeatureFlagData struct {
329 StorageMax uint64 `json:"storage_max" db:"storage_max"`
330 FileMax int64 `json:"file_max" db:"file_max"`
331 SpecialFileMax int64 `json:"special_file_max" db:"special_file_max"`
332}
333
334// Make the Attrs struct implement the driver.Valuer interface. This method
335// simply returns the JSON-encoded representation of the struct.
336func (p FeatureFlagData) Value() (driver.Value, error) {
337 return json.Marshal(p)
338}
339
340// Make the Attrs struct implement the sql.Scanner interface. This method
341// simply decodes a JSON-encoded value into the struct fields.
342func (p *FeatureFlagData) Scan(value any) error {
343 b, err := tcast(value)
344 if err != nil {
345 return err
346 }
347
348 return json.Unmarshal(b, &p)
349}
350
351type PaymentHistoryData struct {
352 Notes string `json:"notes"`
353 TxID string `json:"tx_id"`
354}
355
356// Make the Attrs struct implement the driver.Valuer interface. This method
357// simply returns the JSON-encoded representation of the struct.
358func (p PaymentHistoryData) Value() (driver.Value, error) {
359 return json.Marshal(p)
360}
361
362// Make the Attrs struct implement the sql.Scanner interface. This method
363// simply decodes a JSON-encoded value into the struct fields.
364func (p *PaymentHistoryData) Scan(value any) error {
365 b, err := tcast(value)
366 if err != nil {
367 return err
368 }
369
370 return json.Unmarshal(b, &p)
371}
372
373type ErrMultiplePublicKeys struct{}
374
375func (m *ErrMultiplePublicKeys) Error() string {
376 return "there are multiple users with this public key, you must provide the username when using SSH: `ssh <user>@<domain>`\n"
377}
378
379type UserStats struct {
380 Prose UserServiceStats
381 Pastes UserServiceStats
382 Feeds UserServiceStats
383 Pages UserServiceStats
384}
385
386type UserServiceStats struct {
387 Service string
388 Num int
389 FirstCreatedAt time.Time
390 LastestCreatedAt time.Time
391 LatestUpdatedAt time.Time
392}
393
394type TunsEventLog struct {
395 ID string `json:"id" db:"id"`
396 ServerID string `json:"server_id" db:"server_id"`
397 Time *time.Time `json:"time" db:"time"`
398 User string `json:"user" db:"user"`
399 UserId string `json:"user_id" db:"user_id"`
400 RemoteAddr string `json:"remote_addr" db:"remote_addr"`
401 EventType string `json:"event_type" db:"event_type"`
402 TunnelID string `json:"tunnel_id" db:"tunnel_id"`
403 TunnelType string `json:"tunnel_type" db:"tunnel_type"`
404 ConnectionType string `json:"connection_type" db:"connection_type"`
405 CreatedAt *time.Time `json:"created_at" db:"created_at"`
406}
407
408type PipeMonitor struct {
409 ID string `json:"id" db:"id"`
410 UserId string `json:"user_id" db:"user_id"`
411 Topic string `json:"topic" db:"topic"`
412 WindowDur time.Duration `json:"window_dur" db:"window_dur"`
413 WindowEnd *time.Time `json:"window_end" db:"window_end"`
414 LastPing *time.Time `json:"last_ping" db:"last_ping"`
415 CreatedAt *time.Time `json:"created_at" db:"created_at"`
416 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
417}
418
419type PipeMonitorHistory struct {
420 ID string `json:"id" db:"id"`
421 MonitorID string `json:"monitor_id" db:"monitor_id"`
422 WindowDur time.Duration `json:"window_dur" db:"window_dur"`
423 WindowEnd *time.Time `json:"window_end" db:"window_end"`
424 LastPing *time.Time `json:"last_ping" db:"last_ping"`
425 CreatedAt *time.Time `json:"created_at" db:"created_at"`
426 UpdatedAt *time.Time `json:"updated_at" db:"updated_at"`
427}
428
429type UptimeResult struct {
430 TotalDuration time.Duration
431 UptimeDuration time.Duration
432 UptimePercent float64
433}
434
435func ComputeUptime(history []*PipeMonitorHistory, from, to time.Time) UptimeResult {
436 totalDuration := to.Sub(from)
437 if totalDuration <= 0 {
438 return UptimeResult{}
439 }
440
441 if len(history) == 0 {
442 return UptimeResult{TotalDuration: totalDuration}
443 }
444
445 type interval struct {
446 start, end time.Time
447 }
448
449 var intervals []interval
450 for _, h := range history {
451 if h.WindowEnd == nil {
452 continue
453 }
454 windowStart := h.WindowEnd.Add(-h.WindowDur)
455 windowEnd := *h.WindowEnd
456
457 if windowStart.Before(from) {
458 windowStart = from
459 }
460 if windowEnd.After(to) {
461 windowEnd = to
462 }
463
464 if windowStart.Before(windowEnd) {
465 intervals = append(intervals, interval{start: windowStart, end: windowEnd})
466 }
467 }
468
469 if len(intervals) == 0 {
470 return UptimeResult{TotalDuration: totalDuration}
471 }
472
473 // Sort by start time
474 for i := range intervals {
475 for j := i + 1; j < len(intervals); j++ {
476 if intervals[j].start.Before(intervals[i].start) {
477 intervals[i], intervals[j] = intervals[j], intervals[i]
478 }
479 }
480 }
481
482 // Merge overlapping intervals
483 merged := []interval{intervals[0]}
484 for _, curr := range intervals[1:] {
485 last := &merged[len(merged)-1]
486 if !curr.start.After(last.end) {
487 if curr.end.After(last.end) {
488 last.end = curr.end
489 }
490 } else {
491 merged = append(merged, curr)
492 }
493 }
494
495 var uptimeDuration time.Duration
496 for _, iv := range merged {
497 uptimeDuration += iv.end.Sub(iv.start)
498 }
499
500 uptimePercent := float64(uptimeDuration) / float64(totalDuration) * 100
501
502 return UptimeResult{
503 TotalDuration: totalDuration,
504 UptimeDuration: uptimeDuration,
505 UptimePercent: uptimePercent,
506 }
507}
508
509func (m *PipeMonitor) Status() error {
510 if m.LastPing == nil {
511 return fmt.Errorf("no ping received yet")
512 }
513 if m.WindowEnd == nil {
514 return fmt.Errorf("window end not set")
515 }
516 now := time.Now().UTC()
517 if now.After(*m.WindowEnd) {
518 return fmt.Errorf(
519 "window expired at %s",
520 m.WindowEnd.UTC().Format("2006-01-02 15:04:05Z"),
521 )
522 }
523 windowStart := m.WindowEnd.Add(-m.WindowDur)
524 lastPingAfterStart := !m.LastPing.Before(windowStart)
525 if !lastPingAfterStart {
526 return fmt.Errorf(
527 "last ping before window start: %s",
528 windowStart.UTC().Format("2006-01-02 15:04:05Z"),
529 )
530 }
531 return nil
532}
533
534func (m *PipeMonitor) GetNextWindow() *time.Time {
535 win := m.WindowEnd.Add(m.WindowDur)
536 return &win
537}
538
539var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")
540var DenyList = []string{
541 "admin",
542 "abuse",
543 "cgi",
544 "ops",
545 "help",
546 "spec",
547 "root",
548 "new",
549 "create",
550 "www",
551 "public",
552 "global",
553 "g",
554 "root",
555 "localhost",
556 "ams",
557 "ash",
558 "nue",
559}
560
561type DB interface {
562 RegisterUser(name, pubkey, comment string) (*User, error)
563 UpdatePublicKey(pubkeyID, name string) (*PublicKey, error)
564 InsertPublicKey(userID, pubkey, name string) error
565 FindKeysByUser(user *User) ([]*PublicKey, error)
566 RemoveKeys(pubkeyIDs []string) error
567
568 FindUsers() ([]*User, error)
569 FindUserByName(name string) (*User, error)
570 FindUserByKey(name string, pubkey string) (*User, error)
571 FindUserByPubkey(pubkey string) (*User, error)
572 FindUser(userID string) (*User, error)
573
574 FindUserByToken(token string) (*User, error)
575 FindTokensByUser(userID string) ([]*Token, error)
576 InsertToken(userID, name string) (string, error)
577 UpsertToken(userID, name string) (string, error)
578 RemoveToken(tokenID string) error
579
580 FindPosts() ([]*Post, error)
581 FindPost(postID string) (*Post, error)
582 FindPostsByUser(pager *Pager, userID string, space string) (*Paginate[*Post], error)
583 FindAllPostsByUser(userID string, space string) ([]*Post, error)
584 FindUsersWithPost(space string) ([]*User, error)
585 FindExpiredPosts(space string) ([]*Post, error)
586 FindPostWithFilename(filename string, userID string, space string) (*Post, error)
587 FindPostWithSlug(slug string, userID string, space string) (*Post, error)
588 FindPostsByFeed(pager *Pager, space string) (*Paginate[*Post], error)
589 InsertPost(post *Post) (*Post, error)
590 UpdatePost(post *Post) (*Post, error)
591 RemovePosts(postIDs []string) error
592
593 ReplaceTagsByPost(tags []string, postID string) error
594 FindUserPostsByTag(pager *Pager, tag, userID, space string) (*Paginate[*Post], error)
595 FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
596 FindPopularTags(space string) ([]string, error)
597 ReplaceAliasesByPost(aliases []string, postID string) error
598
599 InsertVisit(view *AnalyticsVisits) error
600 VisitSummary(opts *SummaryOpts) (*SummaryVisits, error)
601 FindVisitSiteList(opts *SummaryOpts) ([]*VisitUrl, error)
602 VisitUrlNotFound(opts *SummaryOpts) ([]*VisitUrl, error)
603
604 AddPicoPlusUser(username, email, paymentType, txId string) error
605 FindFeature(userID string, feature string) (*FeatureFlag, error)
606 FindFeaturesByUser(userID string) ([]*FeatureFlag, error)
607 HasFeatureByUser(userID string, feature string) bool
608
609 InsertFeature(userID, name string, expiresAt time.Time) (*FeatureFlag, error)
610 RemoveFeature(userID, names string) error
611
612 InsertFeedItems(postID string, items []*FeedItem) error
613 FindFeedItemsByPostID(postID string) ([]*FeedItem, error)
614
615 UpsertProject(userID, name, projectDir string) (*Project, error)
616 FindProjectByName(userID, name string) (*Project, error)
617
618 FindUserStats(userID string) (*UserStats, error)
619
620 InsertTunsEventLog(log *TunsEventLog) error
621 FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
622 FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
623
624 InsertAccessLog(log *AccessLog) error
625 FindAccessLogs(userID string, fromDate *time.Time) ([]*AccessLog, error)
626 FindPubkeysInAccessLogs(userID string) ([]string, error)
627 FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*AccessLog, error)
628
629 UpsertPipeMonitor(userID, topic string, dur time.Duration, winEnd *time.Time) error
630 UpdatePipeMonitorLastPing(userID, topic string, lastPing *time.Time) error
631 RemovePipeMonitor(userID, topic string) error
632 FindPipeMonitorByTopic(userID, topic string) (*PipeMonitor, error)
633 FindPipeMonitorsByUser(userID string) ([]*PipeMonitor, error)
634
635 InsertPipeMonitorHistory(monitorID string, windowDur time.Duration, windowEnd, lastPing *time.Time) error
636 FindPipeMonitorHistory(monitorID string, from, to time.Time) ([]*PipeMonitorHistory, error)
637
638 Close() error
639}