type Entry struct { ID uuid.UUID `db:"id" json:"id"` TrackerID uuid.UUID `db:"tracker_id" json:"trackerId"` Interval int `db:"interval" json:"interval"` PerformedAt time.Time `db:"performed_at" json:"performedAt"` } func Create(db *sqlx.DB, e Entry) (Entry, error) { q := `INSERT INTO entries (tracker_id, interval) VALUES ($1, $2) RETURNING *` var newE Entry err := db.QueryRow(q, e.TrackerID, e.Interval). Scan(&newE.ID, &newE.TrackerID) return newE, err } type RateLimiter struct { mu sync.Mutex entries map[string][]time.Time limit int window time.Duration } func (rl *RateLimiter) Allow(key string) bool { rl.mu.Lock() defer rl.mu.Unlock() now := time.Now() cutoff := now.Add(-rl.window) valid := timestamps[:0] for _, t := range timestamps { if t.After(cutoff) { valid = append(valid, t) } } return len(valid) < rl.limit } func NewHTTPHandler(s *server.Service) http.Handler { mux := http.NewServeMux() mux.HandleFunc("GET /health", Healthcheck) mux.HandleFunc("/authenticate", s.MagicLinkHandler) mux.HandleFunc("GET /trackers", s.RequireAuthentication(s.GetAllHandler)) mux.HandleFunc("POST /trackers", s.RequireAuthentication(s.NewHandler)) mux.HandleFunc("GET /vacations", s.RequireAuthentication(s.GetVacationsHandler)) return corsMiddleware(authMiddleware(mux)) } func NewWorkout(db *sqlx.DB, userID uuid.UUID) (Workout, error) { q := `INSERT INTO gym_workouts (user_id) VALUES ($1) RETURNING *` var w Workout err := db.QueryRow(q, userID).Scan( &w.ID, &w.UserID, &w.StartTime, ) w.Sets = []Set{} return w, nil } func (s *Service) LogMarketPriceHandler(w http.ResponseWriter, r *http.Request) { userID, err := s.GetUserIDFromContext(r.Context()) if err != nil { response.RespondWithError(w, http.StatusUnauthorized, "unauthorized") return } var input market.Input json.NewDecoder(r.Body).Decode(&input) input.ItemName = strings.TrimSpace(input.ItemName) } type ScannedItem struct { ItemName string `json:"itemName"` Price float64 `json:"price"` Category *string `json:"category,omitempty"` IsPromo bool `json:"isPromo"` Quantity *float64 `json:"quantity,omitempty"` } type Service struct { Client *stytchapi.API DB *sqlx.DB Notifier *notifier.FCMClient ScanLimiter *RateLimiter AllowedOrigins []string } const safeFetch: typeof fetch = async (input, init) => { try { return await fetch(input, init) } catch (err) { console.error("Network error:", err) return new Response(null, { status: 503 }) } } export const api = ky.create({ prefix: apiUrl, throwHttpErrors: false, credentials: "include", fetch: safeFetch, }) if (Capacitor.getPlatform() === "android") { const cookies = await CapacitorCookies.getCookies({ url: cookieUrl }) const cookieString = Object.entries(cookies) .map(([key, value]) => `${key}=${value}`) .join("; ") request.headers.set("Cookie", cookieString) } func (s *Service) NewHandler(w http.ResponseWriter, r *http.Request) { userID, _ := s.GetUserIDFromContext(r.Context()) familyID, _ := user.GetUserFamilyID(s.DB, userID) var input tracker.Input json.NewDecoder(r.Body).Decode(&input) t := tracker.Tracker{ Owner: userID, Family: familyID, Name: input.Name, Kind: input.Kind, } } type UserManager interface { GetInternalUserID(db *sqlx.DB, email string) (uuid.UUID, error) SyncUserInternal(db *sqlx.DB, email string, createdAt time.Time) (bool, uuid.UUID, error) Get(db *sqlx.DB, email string) (user.User, error) } func GetAllWorkouts(db *sqlx.DB, userID uuid.UUID) ([]Workout, error) { q := `SELECT * FROM gym_workouts WHERE user_id = $1 ORDER BY start_time DESC` var workouts []Workout db.Select(&workouts, q, userID) ids := make([]uuid.UUID, len(workouts)) for i, w := range workouts { ids[i] = w.ID workouts[i].Sets = []Set{} } return workouts, nil } type Tracker struct { ID uuid.UUID `json:"id" db:"id"` Owner uuid.UUID `json:"-" db:"owner_id"` Family uuid.UUID `json:"familyId" db:"family_id"` Name string `json:"name" db:"name"` Display string `json:"display" db:"display"` Interval int `json:"interval" db:"interval"` IntervalUnit string `json:"intervalUnit" db:"interval_unit"` Category string `json:"category" db:"category"` Kind string `json:"kind" db:"kind"` Pinned bool `json:"pinned" db:"pinned"` Show bool `json:"show" db:"show"` Icon string `json:"icon" db:"icon"` StartDate *time.Time `json:"startDate,omitempty" db:"start_date"` Cost *float64 `json:"cost,omitempty" db:"cost"` IsMuted bool `json:"isMuted" db:"is_muted"` } func New(db *sqlx.DB, t Tracker) (uuid.UUID, error) { var newID uuid.UUID q := `INSERT INTO trackers ( owner_id, family_id, name, display, interval, category, kind, action_label, pinned, show, icon ) VALUES ( :owner_id, :family_id, :name, :display, :interval, :category, :kind, :action_label, :pinned, :show, :icon ) RETURNING id` rows, err := db.NamedQuery(q, t) if err != nil { return newID, fmt.Errorf("new tracker: %w", err) } defer rows.Close() if rows.Next() { rows.Scan(&newID) } return newID, nil } func Edit(db *sqlx.DB, t Tracker) error { q := `UPDATE trackers SET name = :name, display = :display, interval = :interval, interval_unit = :interval_unit, category = :category, kind = :kind, pinned = :pinned, show = :show, icon = :icon, start_date = :start_date, cost = :cost, updated_at = NOW() WHERE id = :id` if _, err := db.NamedExec(q, t); err != nil { return fmt.Errorf("edit tracker: %w", err) } return nil } func GetAll(db *sqlx.DB, userID uuid.UUID) ([]Tracker, error) { var t []Tracker q := `SELECT t.*, f.name AS family_name FROM trackers t JOIN families f ON t.family_id = f.id LEFT JOIN tracker_user_settings tus ON tus.user_id = $1 AND tus.tracker_id = t.id WHERE t.owner_id = $1 OR t.family_id IN ( SELECT family_id FROM families_users WHERE user_id = $1 ) ORDER BY t.pinned DESC, t.name ASC` if err := db.Select(&t, q, userID); err != nil { return nil, fmt.Errorf("select trackers: %w", err) } return t, nil } type Workout struct { ID uuid.UUID `db:"id" json:"id"` UserID uuid.UUID `db:"user_id" json:"userId"` StartTime time.Time `db:"start_time" json:"startTime"` Notes *string `db:"notes" json:"notes"` CreatedAt time.Time `db:"created_at" json:"createdAt"` UpdatedAt time.Time `db:"updated_at" json:"updatedAt"` Sets []Set `db:"-" json:"sets"` } type Set struct { ID uuid.UUID `db:"id" json:"id"` WorkoutID uuid.UUID `db:"workout_id" json:"workoutId"` ExerciseID string `db:"exercise_id" json:"exerciseId"` WeightKg *float64 `db:"weight_kg" json:"weightKg"` Reps *int16 `db:"reps" json:"reps"` SetType string `db:"set_type" json:"setType"` IsCompleted bool `db:"is_completed" json:"isCompleted"` Position int16 `db:"position" json:"position"` } type Routine struct { ID uuid.UUID `db:"id" json:"id"` UserID uuid.UUID `db:"user_id" json:"userId"` Name string `db:"name" json:"name"` Position int16 `db:"position" json:"position"` Exercises []RoutineExercise `db:"-" json:"exercises"` } type MarketPrice struct { ID uuid.UUID `json:"id" db:"id"` FamilyID uuid.UUID `json:"-" db:"family_id"` LoggedBy *uuid.UUID `json:"loggedBy" db:"logged_by"` ItemName string `json:"itemName" db:"item_name"` Category *string `json:"category" db:"category"` Store *string `json:"store" db:"store"` Unit *string `json:"unit" db:"unit"` Quantity *float64 `json:"quantity" db:"quantity"` Price float64 `json:"price" db:"price"` IsPromo bool `json:"isPromo" db:"is_promo"` } func LogPrice(ctx context.Context, db *sqlx.DB, p MarketPrice) (UpsertResult, error) { p.ItemName = titleCase(p.ItemName) p.ItemName = canonicalizeItemName(ctx, db, p.FamilyID, p.ItemName) tx, err := db.Beginx() if err != nil { return result, fmt.Errorf("begin tx: %w", err) } defer tx.Rollback() checkQ := `SELECT id FROM market_prices WHERE family_id = $1 AND LOWER(item_name) = LOWER($2) AND price = $3 AND DATE(created_at) = CURRENT_DATE LIMIT 1` err = tx.Get(&existingID, checkQ, p.FamilyID, p.ItemName, p.Price) if err := tx.Commit(); err != nil { return result, fmt.Errorf("commit: %w", err) } return result, nil } func GetPrices(db *sqlx.DB, userID uuid.UUID, filter PriceFilter) ([]MarketPrice, error) { q := `SELECT mp.* FROM market_prices mp WHERE mp.family_id IN ( SELECT family_id FROM families_users WHERE user_id = $1 UNION SELECT id FROM families WHERE owner_id = $1 )` if filter.Category != "" { args = append(args, filter.Category) q += fmt.Sprintf(` AND LOWER(mp.category) = LOWER($%d)`, len(args)) } q += ` ORDER BY mp.created_at DESC` return p, nil } type CookieConfig struct { Secure bool SameSite http.SameSite Partitioned bool Domain string Path string HTTPOnly bool } func (s *Service) setSessionCookies(w http.ResponseWriter, r *http.Request, jwt string, token string) { http.SetCookie(w, &http.Cookie{ Name: "stytch_session_jwt", Value: jwt, Path: s.CookieConfig.Path, Domain: domain, HttpOnly: s.CookieConfig.HTTPOnly, Secure: secure, SameSite: sameSite, Partitioned: partitioned, MaxAge: 5 * 60, }) http.SetCookie(w, &http.Cookie{ Name: "stytch_session_token", Value: token, MaxAge: 24 * 30 * 60 * 60, }) } func (UserManager) SyncUserInternal(db *sqlx.DB, email string, createdAt time.Time) (bool, uuid.UUID, error) { var userID uuid.UUID q := `SELECT id FROM users WHERE email=$1` err := db.QueryRow(q, email).Scan(&userID) if err == nil { return false, userID, nil } if err != sql.ErrNoRows { return false, userID, fmt.Errorf("fetch user: %w", err) } qy := `INSERT INTO users (email, created_at) VALUES ($1, $2) RETURNING id` db.QueryRow(qy, email, createdAt).Scan(&userID) f := Family{Name: "Family", OwnerID: userID} NewFamily(db, f) return true, userID, nil } type User struct { ID uuid.UUID `db:"id" json:"id"` Email string `db:"email" json:"email"` Name *string `db:"name" json:"name"` SoundModeQuick string `db:"sound_mode_quick" json:"soundModeQuick"` SoundModeProfile string `db:"sound_mode_profile" json:"soundModeProfile"` TaskLookAheadDays int `db:"task_lookahead_days" json:"taskLookaheadDays"` PreferredCharacter string `db:"preferred_character" json:"preferredCharacter"` } type WorkoutSummary struct { TotalWorkoutsThisMonth int `json:"totalWorkoutsThisMonth"` TotalVolumeThisMonth float64 `json:"totalVolumeThisMonth"` TotalSetsThisMonth int `json:"totalSetsThisMonth"` TopExercises []ExerciseCount `json:"topExercises"` FailureExercises []ExerciseCount `json:"failureExercises"` } func GetSummary(db *sqlx.DB, userID uuid.UUID) (WorkoutSummary, error) { workoutsQ := `SELECT COUNT(*) FROM gym_workouts WHERE user_id = $1 AND start_time >= date_trunc('month', NOW())` db.Get(&summary.TotalWorkoutsThisMonth, workoutsQ, userID) volumeQ := `SELECT COALESCE(SUM(gs.weight_kg * gs.reps), 0), COUNT(*) FROM gym_sets gs JOIN gym_workouts gw ON gs.workout_id = gw.id WHERE gw.user_id = $1 AND gs.weight_kg IS NOT NULL` db.QueryRow(volumeQ, userID).Scan(&summary.TotalVolumeThisMonth, &summary.TotalSetsThisMonth) return summary, nil } func GetAllRoutines(db *sqlx.DB, userID uuid.UUID) ([]Routine, error) { rq := `SELECT * FROM gym_routines WHERE user_id = $1 ORDER BY position ASC, created_at ASC` var routines []Routine db.Select(&routines, rq, userID) ids := make([]uuid.UUID, len(routines)) rMap := make(map[uuid.UUID]int, len(routines)) for i, r := range routines { ids[i] = r.ID rMap[r.ID] = i routines[i].Exercises = []RoutineExercise{} } return routines, nil } func ReorderRoutine(db *sqlx.DB, userID uuid.UUID, input ReorderRoutineInput) error { var current Routine db.Get(&current, `SELECT * FROM gym_routines WHERE id = $1 AND user_id = $2`, input.RoutineID, userID) var neighbor Routine if input.Direction == "up" { nq = `SELECT * FROM gym_routines WHERE user_id = $1 AND position < $2 ORDER BY position DESC LIMIT 1` } else { nq = `SELECT * FROM gym_routines WHERE user_id = $1 AND position > $2 ORDER BY position ASC LIMIT 1` } swapQ := `UPDATE gym_routines SET position = $1::smallint WHERE id = $2` db.Exec(swapQ, neighbor.Position, current.ID) db.Exec(swapQ, current.Position, neighbor.ID) return nil } func StartWorkoutFromRoutine(db *sqlx.DB, userID uuid.UUID, routineID uuid.UUID) (Workout, error) { tx, err := db.Beginx() defer func() { _ = tx.Rollback() }() var exercises []RoutineExercise eq := `SELECT gre.* FROM gym_routine_exercises gre JOIN gym_routines gr ON gre.routine_id = gr.id WHERE gr.id = $1 AND gr.user_id = $2 ORDER BY gre.position ASC` tx.Select(&exercises, eq, routineID, userID) wq := `INSERT INTO gym_workouts (user_id) VALUES ($1) RETURNING *` var w Workout tx.QueryRow(wq, userID).Scan(&w.ID, &w.UserID, &w.StartTime, &w.Notes) w.Sets = []Set{} for _, re := range exercises { for range re.Sets { var s Set tx.QueryRow(setQ, w.ID, re.ExerciseID, lu.WeightKg, lu.Reps, setType, pos).Scan(&s) w.Sets = append(w.Sets, s) pos++ } } tx.Commit() return w, nil } type MarketInsight struct { ItemName string `json:"itemName" db:"item_name"` Category *string `json:"category" db:"category"` LowestPrice float64 `json:"lowestPrice" db:"lowest_price"` LowestStore *string `json:"lowestStore" db:"lowest_store"` LowestDate time.Time `json:"lowestDate" db:"lowest_date"` LatestPrice float64 `json:"latestPrice" db:"latest_price"` LatestStore *string `json:"latestStore" db:"latest_store"` LatestDate time.Time `json:"latestDate" db:"latest_date"` } func GetInsights(db *sqlx.DB, userID uuid.UUID, category string) ([]MarketInsight, error) { latest, err := getLatestPrices(db, userID, category) lowest, err := getLowestPrices(db, userID, category) lowestMap := make(map[itemKey]lowestRow, len(lowest)) for _, r := range lowest { lowestMap[makeKey(r.ItemName, r.Country)] = r } insights := make([]MarketInsight, 0, len(latest)) for _, l := range latest { low := lowestMap[makeKey(l.ItemName, l.Country)] insights = append(insights, MarketInsight{ ItemName: l.ItemName, Category: l.Category, LowestPrice: low.Price, LowestStore: low.Store, LatestPrice: l.Price, LatestStore: l.Store, }) } return insights, nil } func GetCalendarWorkouts(db *sqlx.DB, userID uuid.UUID) ([]WorkoutCalendarEntry, error) { calendarQ := `SELECT gw.id as workout_id, gw.start_time, COUNT(DISTINCT gs.exercise_id) as exercise_count, COUNT(gs.id) as set_count, ARRAY_AGG(DISTINCT gs.exercise_id) as exercise_ids FROM gym_workouts gw LEFT JOIN gym_sets gs ON gs.workout_id = gw.id WHERE gw.user_id = $1 GROUP BY gw.id ORDER BY gw.start_time DESC` var entries []WorkoutCalendarEntry db.Select(&entries, calendarQ, userID) return entries, nil } func CORSMiddleware(s *server.Service, next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { origin := r.Header.Get("Origin") if slices.Contains(s.AllowedOrigins, origin) { w.Header().Set("Access-Control-Allow-Origin", origin) } w.Header().Set("Access-Control-Allow-Credentials", "true") w.Header().Set("Access-Control-Allow-Headers", "Content-Type, Authorization") w.Header().Set("Access-Control-Allow-Methods", "GET, POST, PUT, PATCH, DELETE, OPTIONS") if r.Method == http.MethodOptions { w.WriteHeader(http.StatusOK); return } next.ServeHTTP(w, r) }) } func RequestIDMiddleware(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { u, _ := uuid.NewV7() ctx := context.WithValue(r.Context(), logging.RequestIDKey, u.String()) w.Header().Set("X-Request-ID", u.String()) next.ServeHTTP(w, r.WithContext(ctx)) }) } func Delete(db *sqlx.DB, trackerID uuid.UUID, userID uuid.UUID) error { q := `DELETE FROM trackers WHERE id = $1 AND owner_id = $2` if _, err := db.Exec(q, trackerID, userID); err != nil { return fmt.Errorf("delete tracker: %w", err) } return nil } func TogglePin(db *sqlx.DB, userID uuid.UUID, trackerID uuid.UUID, isPinned bool) error { q := `UPDATE trackers SET pinned = $1 WHERE id = $2 AND owner_id = $3` db.Exec(q, isPinned, trackerID, userID) return nil } func MuteTracker(db *sqlx.DB, trackerID uuid.UUID, userID uuid.UUID, isMuted bool) error { if isMuted { q = `INSERT INTO tracker_user_settings (tracker_id, user_id, is_muted) VALUES ($1, $2, TRUE) ON CONFLICT (tracker_id, user_id) DO UPDATE SET is_muted = TRUE` } if !isMuted { q = `DELETE FROM tracker_user_settings WHERE tracker_id = $1 AND user_id = $2` } db.Exec(q, trackerID, userID) return nil } type LatestEntry struct { Tracker LastEntry *time.Time `db:"last_entry" json:"lastEntry"` LastInterval *int `json:"lastInterval" db:"last_interval"` LastIntervalUnit *string `json:"lastIntervalUnit" db:"last_interval_unit"` DueStatus *string `json:"dueStatus" db:"-"` } func CalculateTrackersLastDue(tDB []LatestEntry) ([]LatestEntry, error) { for i := range tDB { switch tDB[i].IntervalUnit { case "day": threshold = tDB[i].LastEntry.Add(time.Duration(*itv) * 24 * time.Hour) case "month": threshold = tDB[i].LastEntry.AddDate(0, int(*itv), 0) case "year": threshold = tDB[i].LastEntry.AddDate(int(*itv), 0, 0) } if time.Now().After(threshold) { n := "due"; newT[i].DueStatus = &n } } return newT, nil } func EditWorkout(db *sqlx.DB, userID uuid.UUID, workoutID uuid.UUID, w WorkoutInput) error { q := `UPDATE gym_workouts SET start_time = $1, notes = $2, updated_at = NOW() WHERE id = $3 AND user_id = $4` db.Exec(q, w.StartTime, w.Notes, workoutID, userID) return nil } func GetExerciseStats(db *sqlx.DB, userID uuid.UUID, exerciseID string) ([]ExerciseSetStats, error) { query := `SELECT gw.start_time::date as date, gs.weight_kg, gs.reps, gs.set_type FROM gym_sets gs JOIN gym_workouts gw ON gs.workout_id = gw.id WHERE gw.user_id = $1 AND LOWER(gs.exercise_id) = LOWER($2) ORDER BY gw.start_time ASC, gs.position ASC` var stats []ExerciseSetStats db.Select(&stats, query, userID, exerciseID) return stats, nil } func UpdateName(db *sqlx.DB, userID uuid.UUID, name string) error { q := `UPDATE users SET name = $1 WHERE id = $2` db.Exec(q, name, userID) return nil }

Migrating Sveltekit Static Site (adapter-static) from Cloudflare Pages to Workers

29 Sep 2025 / 2 min read

Cloudflare/ Sveltekit/ Workers/ Pages

I’m a longtime fan of Cloudflare Pages. It’s free, it’s fast, and it’s easy to get started. In the last year or so I’ve been reading about Cloudflare Workers, and recently I saw Cloudflare recommending everyone to start with Workers for new projects.

I couldn’t find much online about the specific setup for what I used with Cloudflare Pages, which was Sveltekit using adapter-static. It’s what I’ve always used for all my static sites.

After some digging, it turns out all I needed is to:

Add the static assets config (not the full fledged one) of wrangler.jsonc to the root of the Sveltekit project

// wrangler.jsonc
{
	"name": "your-name",
	"compatibility_date": "2025-01-01",
	"assets": {
		"directory": "./build/"
	}
}

You’ll need to do largely the same configs like connected domain within the Workers project settings (Compute (Workers) > project-name > Settings)
I use pnpm, so

- Build command: pnpm build
- Deploy command: pnpx wrangler deploy
- Root directory: /

In the settings you’ll see that things like “Variables cannot be added to a Worker that only has static assets”. (They recently changed their UI. Previously the project name would have a pill tag beside saying “Static”)
A reminder though, check your builds. For some reason Cloudflare Pages allowed me to do some dynamic routes and import that adapter-static didn’t. So I had to switch to vite’s import.meta.glob for posts and exporting an entries func to make it work.

All good! Everything’s deployed as static assets. When I first tried it using adapter-cloudflare I still ended up invoking page functions for some reason.

Overall, it was an easy config change. I still very much prefer the Pages UI and settings, which are much more straightforward. Cloudflare Workers’ graphs, charts, dashboard are very cluttered imo and not enjoyable to work with.

But Cloudflare services are free, so I’m not complaining. I’d still use this even though I’ve VPSes for deploying more complex projects.

See also: Other Workers config here

— END OF POST —

© Zixian Chen