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