orchard

  1package main
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"encoding/json"
  7	"fmt"
  8	"os"
  9	"path/filepath"
 10	"time"
 11
 12	"github.com/google/uuid"
 13	_ "modernc.org/sqlite"
 14)
 15
 16// There is no migration table. Every statement is IF NOT EXISTS, so this runs
 17// against an existing database as a no-op; a schema change is a new guarded block.
 18const schema = `
 19CREATE TABLE IF NOT EXISTS properties (
 20    id            BLOB PRIMARY KEY,
 21    name          TEXT NOT NULL,
 22    custom_cards  TEXT NOT NULL DEFAULT '[]',
 23    is_protected  INTEGER NOT NULL DEFAULT 0,
 24    is_public     INTEGER NOT NULL DEFAULT 0,
 25    created_at    INTEGER NOT NULL,
 26    updated_at    INTEGER NOT NULL
 27);
 28
 29CREATE TABLE IF NOT EXISTS events (
 30    id              INTEGER PRIMARY KEY AUTOINCREMENT,
 31    property_id     BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
 32    event           TEXT NOT NULL,
 33    created_at      INTEGER NOT NULL,
 34    user_id         TEXT,
 35    url             TEXT,
 36    title           TEXT,
 37    referrer        TEXT,
 38    user_agent      TEXT,
 39    platform        TEXT,
 40    browser         TEXT,
 41    device          TEXT,
 42    screen_width    INTEGER,
 43    screen_height   INTEGER,
 44    country         TEXT,
 45    region          TEXT,
 46    city            TEXT,
 47    lat             REAL,
 48    lon             REAL,
 49    utm_source      TEXT,
 50    utm_medium      TEXT,
 51    utm_campaign    TEXT,
 52    utm_term        TEXT,
 53    utm_content     TEXT,
 54    time_on_page_ms INTEGER,
 55    extra           TEXT NOT NULL DEFAULT '{}'
 56);
 57CREATE INDEX IF NOT EXISTS events_property_created       ON events(property_id, created_at);
 58CREATE INDEX IF NOT EXISTS events_property_event_created ON events(property_id, event, created_at);
 59
 60CREATE TABLE IF NOT EXISTS bot_events (
 61    id           INTEGER PRIMARY KEY AUTOINCREMENT,
 62    property_id  BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
 63    event        TEXT NOT NULL,
 64    created_at   INTEGER NOT NULL,
 65    bot_name     TEXT,
 66    url          TEXT,
 67    user_agent   TEXT,
 68    country      TEXT,
 69    extra        TEXT NOT NULL DEFAULT '{}'
 70);
 71CREATE INDEX IF NOT EXISTS bot_events_property_created ON bot_events(property_id, created_at);
 72
 73`
 74
 75// openDB opens the SQLite database and applies the schema. modernc.org/sqlite is
 76// pure Go, which keeps CGO_ENABLED=0. Pragmas go in the DSN because they are per
 77// connection and database/sql opens connections lazily.
 78func openDB(path string) (*sql.DB, error) {
 79	if dir := filepath.Dir(path); dir != "" {
 80		if err := os.MkdirAll(dir, 0o755); err != nil {
 81			return nil, fmt.Errorf("create data dir: %w", err)
 82		}
 83	}
 84
 85	dsn := path +
 86		"?_pragma=journal_mode(WAL)" +
 87		"&_pragma=synchronous(NORMAL)" +
 88		"&_pragma=busy_timeout(5000)" +
 89		// Lets the retention sweep hand freed pages back. It only takes effect
 90		// on an empty file, so a database made before this keeps auto_vacuum
 91		// NONE and needs a one-off VACUUM to convert.
 92		"&_pragma=auto_vacuum(INCREMENTAL)" +
 93		"&_pragma=foreign_keys(ON)"
 94
 95	db, err := sql.Open("sqlite", dsn)
 96	if err != nil {
 97		return nil, fmt.Errorf("open sqlite: %w", err)
 98	}
 99
100	// SQLite writes under a database-wide lock; the pool is sized for the
101	// readers WAL lets run alongside a write.
102	db.SetMaxOpenConns(8)
103	db.SetMaxIdleConns(8)
104	db.SetConnMaxLifetime(time.Hour)
105
106	if err := db.Ping(); err != nil {
107		return nil, fmt.Errorf("ping sqlite: %w", err)
108	}
109	if _, err := db.Exec(schema); err != nil {
110		return nil, fmt.Errorf("apply schema: %w", err)
111	}
112	if _, err := db.Exec(renameProprium); err != nil {
113		return nil, fmt.Errorf("rename proprium: %w", err)
114	}
115	return db, nil
116}
117
118// renameProprium is a one-time data migration. It is idempotent and matches
119// nothing once it has run, so it can be deleted once every database has.
120const renameProprium = `
121UPDATE properties
122   SET name = 'analytics.bythewood.me',
123       updated_at = CAST(strftime('%s', 'now') AS INTEGER) * 1000
124 WHERE name = 'Proprium';
125DROP TABLE IF EXISTS meta;
126`
127
128// Property is one tracked site.
129type Property struct {
130	ID          uuid.UUID
131	Name        string
132	CustomCards []CustomCard
133	IsProtected bool
134	IsPublic    bool
135	CreatedAt   int64
136	UpdatedAt   int64
137}
138
139// CustomCard is a custom event pinned to the dashboard as a metric card. Value
140// is the pinned flag, named for what the stored JSON already calls it.
141type CustomCard struct {
142	Event string `json:"event"`
143	Value bool   `json:"value"`
144}
145
146const propertyColumns = `id, name, custom_cards, is_protected, is_public, created_at, updated_at`
147
148// scanProperty reads one row of propertyColumns. Malformed custom_cards JSON
149// degrades to no cards rather than failing the request.
150func scanProperty(scan func(...any) error) (*Property, error) {
151	var (
152		p     Property
153		idRaw []byte
154		cards string
155		prot  int64
156		pub   int64
157	)
158	if err := scan(&idRaw, &p.Name, &cards, &prot, &pub, &p.CreatedAt, &p.UpdatedAt); err != nil {
159		return nil, err
160	}
161	id, err := uuid.FromBytes(idRaw)
162	if err != nil {
163		return nil, fmt.Errorf("property id is not a uuid: %w", err)
164	}
165	p.ID = id
166	p.IsProtected = prot != 0
167	p.IsPublic = pub != 0
168	if err := json.Unmarshal([]byte(cards), &p.CustomCards); err != nil {
169		p.CustomCards = nil
170	}
171	return &p, nil
172}
173
174// lookupProperty returns the property with this id, or nil when there is none.
175func lookupProperty(ctx context.Context, db *sql.DB, id uuid.UUID) (*Property, error) {
176	row := db.QueryRowContext(ctx,
177		"SELECT "+propertyColumns+" FROM properties WHERE id = ?", id[:])
178	p, err := scanProperty(row.Scan)
179	if err == sql.ErrNoRows {
180		return nil, nil
181	}
182	return p, err
183}