db.go
1package user
2
3import (
4 "database/sql"
5 "embed"
6 "encoding/json"
7 "errors"
8 "fmt"
9 "log/slog"
10 "regexp"
11 "slices"
12 "strings"
13
14 "git.kilimanjaro.io/rtw/pkg/log"
15 "github.com/alexedwards/argon2id"
16 "github.com/pressly/goose/v3"
17)
18
19//go:embed schema/*.sql
20var migrations embed.FS
21
22var (
23 ErrEmailNotFound = errors.New("email not found")
24 ErrHandleNotFound = errors.New("handle not found")
25 ErrPasswordMismatch = errors.New("password mismatch")
26 ErrInvalidHandle = errors.New("invalid handle")
27 ErrHandleExists = errors.New("handle exists")
28 ErrInvalidEmail = errors.New("account exists for email")
29 ErrAccountExists = errors.New("account exists")
30)
31
32// Migrate sets up the database
33func Migrate(db *sql.DB) error {
34 goose.SetBaseFS(migrations)
35 goose.SetLogger(log.GooseLogger(slog.String("migrations", "pkg/user/schema")))
36
37 if err := goose.SetDialect("sqlite3"); err != nil {
38 return fmt.Errorf("error setting user database type: %w", err)
39 }
40
41 if err := goose.Up(db, "schema", goose.WithNoVersioning()); err != nil {
42 return fmt.Errorf("error migrating user tables: %w", err)
43 }
44
45 return nil
46}
47
48type userDetails struct {
49 email *string
50 handle *string
51 passwordHash *string
52 metadata []byte
53}
54
55// Option sets fields for a user
56type Option func(*userDetails) error
57
58// Email for a user
59func Email(email string) Option {
60 return func(c *userDetails) error {
61 c.email = &email
62 return nil
63 }
64}
65
66// Handle for a user
67func Handle(handle string) Option {
68 return func(c *userDetails) error {
69 if !isHandleValid(handle) {
70 return ErrInvalidHandle
71 }
72 c.handle = &handle
73 return nil
74 }
75}
76
77// Password for a user. Password is hashed using Argon2ID with default parameters for 8/2025
78func Password(password string) Option {
79 return func(c *userDetails) error {
80 hash, err := argon2id.CreateHash(password, argon2id.DefaultParams)
81 if err != nil {
82 return err
83 }
84 c.passwordHash = &hash
85 return nil
86 }
87}
88
89// Metadata for a user. Any value that can be marshaled to JSON.
90func Metadata(md any) Option {
91 return func(c *userDetails) error {
92 b, err := json.Marshal(md)
93 if err != nil {
94 return fmt.Errorf("error marshaling user metadata: %w", err)
95 }
96 c.metadata = b
97 return nil
98 }
99}
100
101// Set details for a user. This is an upsert operation if the id already exists.
102func Set[T ~int64](db *sql.DB, id T, options ...Option) error {
103 var c userDetails
104 for _, opt := range options {
105 if err := opt(&c); err != nil {
106 return err
107 }
108 }
109
110 if _, err := db.Exec(`INSERT INTO users
111 (id, handle, email, password_hash, metadata)
112 VALUES
113 (?, ?, ?, ?, ?)
114 ON CONFLICT (id) DO UPDATE SET
115 handle=COALESCE(excluded.handle, handle),
116 email=COALESCE(excluded.email, email),
117 password_hash=COALESCE(excluded.password_hash, password_hash),
118 metadata=COALESCE(excluded.metadata, metadata)`,
119 int64(id), c.handle, c.email, c.passwordHash, c.metadata); err != nil {
120 return fmt.Errorf("error inserting user record: %w", err)
121 }
122 return nil
123}
124
125// Login checks the email or handle with the password and returns the user ID or an error on password mismatch or non-existent account identifier.
126func Login[T ~int64](db *sql.DB, emailOrHandle string, password string) (T, error) {
127 // try email first then handle if email not found
128 id, err := loginEmail[T](db, emailOrHandle, password)
129 if err != nil && errors.Is(err, ErrEmailNotFound) {
130 return loginHandle[T](db, emailOrHandle, password)
131 }
132 return id, err
133}
134
135func loginEmail[T ~int64](db *sql.DB, email string, password string) (T, error) {
136 var id int64
137 var hash string
138 if err := db.QueryRow("SELECT id, password_hash FROM users_normalized WHERE email=lower(?)", email).Scan(&id, &hash); err != nil {
139 return T(-1), ErrEmailNotFound
140 }
141 match, err := argon2id.ComparePasswordAndHash(password, hash)
142 if match && err == nil {
143 return T(id), nil
144 }
145 return T(-1), ErrPasswordMismatch
146}
147
148func loginHandle[T ~int64](db *sql.DB, handle string, password string) (T, error) {
149 var id int64
150 var hash string
151 if err := db.QueryRow("SELECT id, password_hash FROM users_normalized WHERE handle=lower(?)", handle).Scan(&id, &hash); err != nil {
152 return T(-1), ErrHandleNotFound
153 }
154 match, err := argon2id.ComparePasswordAndHash(password, hash)
155 if match && err == nil {
156 return T(id), nil
157 }
158 return T(-1), ErrPasswordMismatch
159}
160
161// isHandleValid checks whether the handle meets the requirements and is not in the blocklist
162func isHandleValid(handle string) bool {
163 blocklist := []string{
164 "moderator",
165 "founder",
166 "rtw.run",
167 "ewan",
168 "charley",
169 "[deleted]",
170 "scooter",
171 "bt",
172 }
173 r := regexp.MustCompile(`^(([a-zA-Z0-9]+|(\.|_|\-|\/|@|\[|\]|!)+))+$`)
174 if len(handle) < 2 || r.FindString(handle) != handle || slices.Contains(blocklist, strings.ToLower(handle)) {
175 return false
176 }
177 return true
178}
179
180// CheckEmail returns an ErrAccountExists if the email is already in the database
181func CheckEmail(db *sql.DB, email string) error {
182 if !strings.Contains(email, "@") || email == "" {
183 return ErrInvalidEmail
184 }
185 var exists bool
186 if err := db.QueryRow("SELECT COUNT(email) FROM users_normalized WHERE email=lower(?)", email).Scan(&exists); err != nil {
187 return fmt.Errorf("unexpected error checking email: %w", err)
188 }
189 if exists {
190 return ErrAccountExists
191 }
192 return nil
193}
194
195// CheckHandle returns ErrHandleExists if the handle has been previously taken or ErrInvalidHandle if the handle doesnt meet handle rules or in the blocklist
196func CheckHandle(db *sql.DB, handle string) error {
197 if !isHandleValid(handle) {
198 return ErrInvalidHandle
199 }
200 var exists bool
201 if err := db.QueryRow("SELECT COUNT(handle) FROM users_normalized WHERE handle=lower(?)", handle).Scan(&exists); err != nil {
202 return fmt.Errorf("unexpected error checking handle: %w", err)
203 }
204 if exists {
205 return ErrHandleExists
206 }
207 return nil
208}
209
210// HandleRules returns the rules for a valid handle
211func HandleRules() []string {
212 return []string{
213 "Must start with a letter or number",
214 "May contain punctuation: . / _ - @ [] !",
215 "Can not start or end with a punctuation character",
216 "Must be at least 2 characters long",
217 }
218}
219
220// GetEmailAndHandle gets the email and handle for a user ID
221func GetEmailAndHandle[T ~int64](db *sql.DB, id T) (email string, handle string, err error) {
222 var e, h sql.NullString
223 err = db.QueryRow("SELECT email, handle FROM users WHERE id=?", int64(id)).Scan(&e, &h)
224 if e.Valid {
225 email = e.String
226 }
227 if h.Valid {
228 handle = h.String
229 }
230 return
231}
232
233// GetMetadata returns the metadata, unmarshaling it from JSON into receiver
234func GetMetadata[T ~int64](db *sql.DB, id T, receiver any) error {
235 var b []byte
236 if err := db.QueryRow("SELECT metadata FROM users WHERE id=?", int64(id)).Scan(&b); err != nil {
237 return fmt.Errorf("error getting metadata for id=%d: %w", id, err)
238 }
239 if err := json.Unmarshal(b, receiver); err != nil {
240 return fmt.Errorf("error unmarshaling metadata: %w", err)
241 }
242 return nil
243}
244
245// IsRegistered returns true if the user ID is associated with an account that has either an email or handle
246func IsRegistered[T ~int64](db *sql.DB, id T) (bool, error) {
247 var ok bool
248 if err := db.QueryRow("SELECT COUNT(*) FROM users_registered WHERE id=?", int64(id)).Scan(&ok); err != nil {
249 return false, fmt.Errorf("error checking registered status for user=%d: %w", id, err)
250 }
251 return ok, nil
252}
253
254// IsAnonymous returns true if the user ID does not have an associated email or handle
255func IsAnonymous[T ~int64](db *sql.DB, id T) (bool, error) {
256 ok, err := IsRegistered(db, id)
257 return !ok, err
258}
