orchard

  1package main
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"encoding/json"
  7	"fmt"
  8	"log/slog"
  9	"math"
 10	"math/rand/v2"
 11	"time"
 12
 13	"github.com/google/uuid"
 14)
 15
 16// Dev tooling: fills a "Seed Test" property with fake traffic so the dashboard
 17// has something to render. It is a flag on the site binary rather than its own
 18// cmd/, since it needs the schema, connection settings and column list.
 19
 20const seedPropertyName = "Seed Test"
 21
 22type weightedURL struct {
 23	Path   string
 24	Title  string
 25	Weight int
 26}
 27
 28var seedURLs = []weightedURL{
 29	{"/", "Home", 40},
 30	{"/about", "About", 10},
 31	{"/pricing", "Pricing", 8},
 32	{"/docs", "Documentation", 8},
 33	{"/blog", "Blog", 5},
 34	{"/blog/getting-started", "Getting Started", 5},
 35	{"/blog/whats-new-in-v2", "What's New in v2", 4},
 36	{"/blog/case-studies", "Case Studies", 3},
 37	{"/contact", "Contact", 4},
 38	{"/login", "Sign In", 4},
 39	{"/signup", "Sign Up", 4},
 40	{"/dashboard", "Dashboard", 5},
 41}
 42
 43type weightedString struct {
 44	Value  string
 45	Weight int
 46}
 47
 48// The empty referrer is the heaviest entry; most real traffic is direct or has
 49// its referrer stripped.
 50var seedReferrers = []weightedString{
 51	{"", 50}, {"google.com", 20}, {"twitter.com", 5}, {"news.ycombinator.com", 3},
 52	{"github.com", 3}, {"reddit.com", 4}, {"duckduckgo.com", 3}, {"bing.com", 3},
 53	{"linkedin.com", 2}, {"producthunt.com", 2}, {"dev.to", 2}, {"medium.com", 1},
 54}
 55
 56type seedAgent struct {
 57	UA       string
 58	Platform string
 59	Browser  string
 60	Device   string
 61	IsBot    bool
 62	BotName  string
 63	Weight   int
 64}
 65
 66var seedAgents = []seedAgent{
 67	{UA: "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36",
 68		Platform: "Windows", Browser: "Chrome", Device: "Desktop", Weight: 25},
 69	{UA: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36",
 70		Platform: "Mac OS X", Browser: "Chrome", Device: "Desktop", Weight: 15},
 71	{UA: "Mozilla/5.0 (Linux; Android 14; Pixel 8) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Mobile Safari/537.36",
 72		Platform: "Android", Browser: "Chrome Mobile", Device: "Mobile", Weight: 15},
 73	{UA: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.4 Safari/605.1.15",
 74		Platform: "Mac OS X", Browser: "Safari", Device: "Desktop", Weight: 10},
 75	{UA: "Mozilla/5.0 (iPhone; CPU iPhone OS 17_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.4 Mobile/15E148 Safari/604.1",
 76		Platform: "iOS", Browser: "Mobile Safari", Device: "Mobile", Weight: 15},
 77	{UA: "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0",
 78		Platform: "Windows", Browser: "Firefox", Device: "Desktop", Weight: 5},
 79	{UA: "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:128.0) Gecko/20100101 Firefox/128.0",
 80		Platform: "Ubuntu", Browser: "Firefox", Device: "Desktop", Weight: 3},
 81	{UA: "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36 Edg/131.0.0.0",
 82		Platform: "Windows", Browser: "Edge", Device: "Desktop", Weight: 8},
 83	{UA: "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)",
 84		IsBot: true, BotName: "Googlebot", Weight: 1},
 85	{UA: "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)",
 86		IsBot: true, BotName: "bingbot", Weight: 1},
 87	{UA: "facebookexternalhit/1.1 (+http://www.facebook.com/externalhit_uatext.php)",
 88		IsBot: true, BotName: "facebookexternalhit", Weight: 1},
 89}
 90
 91type seedGeo struct {
 92	Country string
 93	Region  string
 94	City    string
 95	Lat     float64
 96	Lon     float64
 97	Weight  int
 98}
 99
100// Region names are the English forms the admin-1 topojson joins on.
101var seedGeos = []seedGeo{
102	{"US", "New York", "New York", 40.7128, -74.0060, 15},
103	{"US", "California", "Los Angeles", 34.0522, -118.2437, 10},
104	{"US", "California", "San Francisco", 37.7749, -122.4194, 8},
105	{"US", "Illinois", "Chicago", 41.8781, -87.6298, 5},
106	{"US", "Texas", "Austin", 30.2672, -97.7431, 5},
107	{"GB", "England", "London", 51.5074, -0.1278, 8},
108	{"GB", "England", "Manchester", 53.4808, -2.2426, 2},
109	{"DE", "Berlin", "Berlin", 52.5200, 13.4050, 5},
110	{"DE", "Bavaria", "Munich", 48.1351, 11.5820, 3},
111	{"FR", "Île-de-France", "Paris", 48.8566, 2.3522, 5},
112	{"CA", "Ontario", "Toronto", 43.6532, -79.3832, 4},
113	{"CA", "British Columbia", "Vancouver", 49.2827, -123.1207, 2},
114	{"AU", "New South Wales", "Sydney", -33.8688, 151.2093, 3},
115	{"AU", "Victoria", "Melbourne", -37.8136, 144.9631, 2},
116	{"JP", "Tokyo", "Tokyo", 35.6762, 139.6503, 4},
117	{"BR", "São Paulo", "São Paulo", -23.5505, -46.6333, 3},
118	{"IN", "Maharashtra", "Mumbai", 19.0760, 72.8777, 3},
119	{"IN", "Karnataka", "Bangalore", 12.9716, 77.5946, 3},
120	{"NL", "North Holland", "Amsterdam", 52.3676, 4.9041, 3},
121	{"ES", "Madrid", "Madrid", 40.4168, -3.7038, 2},
122	{"IT", "Lazio", "Rome", 41.9028, 12.4964, 2},
123	{"MX", "Mexico City", "Mexico City", 19.4326, -99.1332, 2},
124	{"KR", "Seoul", "Seoul", 37.5665, 126.9780, 2},
125	{"SE", "Stockholm", "Stockholm", 59.3293, 18.0686, 2},
126	{"PL", "Mazovia", "Warsaw", 52.2297, 21.0122, 2},
127	{"TR", "Istanbul", "Istanbul", 41.0082, 28.9784, 2},
128	{"ZA", "Gauteng", "Johannesburg", -26.2041, 28.0473, 1},
129}
130
131var (
132	seedDesktopScreens = [][2]int64{{1920, 1080}, {1366, 768}, {1440, 900}, {1536, 864}, {1680, 1050}, {2560, 1440}}
133	seedMobileScreens  = [][2]int64{{390, 844}, {414, 896}, {375, 667}, {360, 800}, {412, 915}, {393, 851}}
134	seedUTMSources     = []string{"google", "twitter", "hn", "newsletter", "github", "producthunt"}
135	seedUTMMediums     = []string{"cpc", "social", "email", "referral", "organic"}
136	seedUTMCampaigns   = []string{"launch-2026", "spring-promo", "blog-feature", "rebrand", "retarget"}
137)
138
139// Demo custom events, each with a per-session probability.
140var seedCustomEvents = []struct {
141	Name        string
142	Probability float64
143}{
144	{"signup", 0.05},
145	{"checkout_success", 0.02},
146	{"signup_cta_click", 0.08},
147}
148
149func weightedPick[T any](items []T, weight func(T) int) T {
150	total := 0
151	for _, it := range items {
152		total += weight(it)
153	}
154	pick := rand.IntN(total)
155	for _, it := range items {
156		w := weight(it)
157		if pick < w {
158			return it
159		}
160		pick -= w
161	}
162	return items[len(items)-1]
163}
164
165// runSeed wipes and refills the Seed Test property, reusing it so the dashboard
166// URL stays stable across seeds.
167func runSeed(ctx context.Context, db *sql.DB, sessions, days int) error {
168	id, err := ensureSeedProperty(ctx, db)
169	if err != nil {
170		return err
171	}
172
173	for _, table := range []string{"events", "bot_events"} {
174		if _, err := db.ExecContext(ctx,
175			"DELETE FROM "+table+" WHERE property_id = ?", id[:]); err != nil {
176			return fmt.Errorf("clear %s: %w", table, err)
177		}
178	}
179
180	cards := make([]CustomCard, 0, len(seedCustomEvents))
181	for _, ce := range seedCustomEvents {
182		cards = append(cards, CustomCard{Event: ce.Name, Value: true})
183	}
184	encoded, _ := json.Marshal(cards)
185	if _, err := db.ExecContext(ctx,
186		"UPDATE properties SET custom_cards = ?, updated_at = ? WHERE id = ?",
187		string(encoded), time.Now().UnixMilli(), id[:]); err != nil {
188		return fmt.Errorf("set custom cards: %w", err)
189	}
190
191	total, err := generateSeed(ctx, db, id, sessions, days)
192	if err != nil {
193		return err
194	}
195
196	slog.Info(fmt.Sprintf("seeded %d sessions (%d events) into %q (%s)", sessions, total, seedPropertyName, id))
197	slog.Info(fmt.Sprintf("dashboard: http://localhost:8000/%s", id))
198	return nil
199}
200
201func ensureSeedProperty(ctx context.Context, db *sql.DB) (uuid.UUID, error) {
202	var raw []byte
203	err := db.QueryRowContext(ctx,
204		"SELECT id FROM properties WHERE name = ?", seedPropertyName).Scan(&raw)
205	if err == nil {
206		return uuid.FromBytes(raw)
207	}
208	if err != sql.ErrNoRows {
209		return uuid.Nil, err
210	}
211
212	id := uuid.New()
213	now := time.Now().UnixMilli()
214	_, err = db.ExecContext(ctx,
215		`INSERT INTO properties (id, name, custom_cards, is_protected, is_public, created_at, updated_at)
216		 VALUES (?, ?, '[]', 0, 0, ?, ?)`,
217		id[:], seedPropertyName, now, now)
218	return id, err
219}
220
221func generateSeed(ctx context.Context, db *sql.DB, id uuid.UUID, sessions, days int) (int64, error) {
222	// One transaction for the whole run; otherwise every insert is its own
223	// commit and its own fsync.
224	tx, err := db.BeginTx(ctx, nil)
225	if err != nil {
226		return 0, err
227	}
228	defer tx.Rollback()
229
230	human, err := tx.PrepareContext(ctx, `INSERT INTO events (
231	    property_id, event, created_at, user_id, url, title, referrer, user_agent,
232	    platform, browser, device, screen_width, screen_height, country, region, city,
233	    lat, lon, utm_source, utm_medium, utm_campaign, time_on_page_ms, extra
234	  ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'{}')`)
235	if err != nil {
236		return 0, err
237	}
238	defer human.Close()
239
240	bot, err := tx.PrepareContext(ctx, `INSERT INTO bot_events (
241	    property_id, event, created_at, bot_name, url, user_agent, country, extra
242	  ) VALUES (?,?,?,?,?,?,?,'{}')`)
243	if err != nil {
244		return 0, err
245	}
246	defer bot.Close()
247
248	now := time.Now().UnixMilli()
249	windowMS := int64(days) * 24 * 60 * 60 * 1000
250	var total int64
251
252	for i := 0; i < sessions; i++ {
253		agent := weightedPick(seedAgents, func(a seedAgent) int { return a.Weight })
254		geo := weightedPick(seedGeos, func(g seedGeo) int { return g.Weight })
255		referrer := weightedPick(seedReferrers, func(r weightedString) int { return r.Weight }).Value
256
257		userID := fmt.Sprintf("%d", 100_000_000+rand.Int64N(899_999_999))
258
259		// Bias toward recent days, or the default window compares a full
260		// period against a nearly empty one and shows +1000% everywhere.
261		offset := int64(math.Pow(rand.Float64(), 1.15) * float64(windowMS))
262		sessionStart := now - offset
263
264		url := weightedPick(seedURLs, func(u weightedURL) int { return u.Weight })
265
266		if agent.IsBot {
267			if _, err := bot.ExecContext(ctx, id[:], "page_view", sessionStart,
268				agent.BotName, url.Path, agent.UA, geo.Country); err != nil {
269				return total, err
270			}
271			total++
272			continue
273		}
274
275		screens := seedDesktopScreens
276		if agent.Device == "Mobile" {
277			screens = seedMobileScreens
278		}
279		screen := screens[rand.IntN(len(screens))]
280
281		var utmSource, utmMedium, utmCampaign any
282		if rand.Float64() < 0.3 {
283			utmSource = seedUTMSources[rand.IntN(len(seedUTMSources))]
284			utmMedium = seedUTMMediums[rand.IntN(len(seedUTMMediums))]
285			utmCampaign = seedUTMCampaigns[rand.IntN(len(seedUTMCampaigns))]
286		}
287
288		insert := func(event string, at int64, url weightedURL, ref any, utmS, utmM, utmC any, timeOnPage any) error {
289			_, err := human.ExecContext(ctx, id[:], event, at, userID, url.Path, url.Title,
290				ref, agent.UA, agent.Platform, agent.Browser, agent.Device,
291				screen[0], screen[1], geo.Country, geo.Region, geo.City, geo.Lat, geo.Lon,
292				utmS, utmM, utmC, timeOnPage)
293			return err
294		}
295
296		var sessionRef any
297		if referrer != "" {
298			sessionRef = referrer
299		}
300
301		if err := insert("session_start", sessionStart, url, sessionRef, utmSource, utmMedium, utmCampaign, nil); err != nil {
302			return total, err
303		}
304		total++
305
306		for _, ce := range seedCustomEvents {
307			if rand.Float64() < ce.Probability {
308				at := sessionStart + 1_000 + rand.Int64N(29_000)
309				if err := insert(ce.Name, at, url, nil, nil, nil, nil, nil); err != nil {
310					return total, err
311				}
312				total++
313			}
314		}
315
316		t := sessionStart
317		pageCount := 1 + rand.IntN(8)
318		for page := 0; page < pageCount; page++ {
319			timeOnPage := 2_000 + rand.Int64N(118_000)
320
321			// Only the first page view of a session carries the referrer.
322			var pvRef any
323			if page == 0 {
324				pvRef = sessionRef
325			}
326			if err := insert("page_view", t, url, pvRef, utmSource, utmMedium, utmCampaign, nil); err != nil {
327				return total, err
328			}
329			total++
330
331			if rand.Float64() < 0.4 {
332				if err := insert("click", t+500+rand.Int64N(timeOnPage), url, nil, nil, nil, nil, nil); err != nil {
333					return total, err
334				}
335				total++
336			}
337
338			if err := insert("page_leave", t+timeOnPage, url, nil, nil, nil, nil, timeOnPage); err != nil {
339				return total, err
340			}
341			total++
342
343			t += timeOnPage + 500 + rand.Int64N(2_500)
344			if page+1 < pageCount {
345				url = weightedPick(seedURLs, func(u weightedURL) int { return u.Weight })
346			}
347		}
348	}
349
350	return total, tx.Commit()
351}