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