analytics-rust

 1CREATE TABLE properties (
 2    id            BLOB PRIMARY KEY,
 3    name          TEXT NOT NULL,
 4    custom_cards  TEXT NOT NULL DEFAULT '[]',
 5    is_protected  INTEGER NOT NULL DEFAULT 0,
 6    is_public     INTEGER NOT NULL DEFAULT 0,
 7    created_at    INTEGER NOT NULL,
 8    updated_at    INTEGER NOT NULL
 9);
10
11CREATE TABLE events (
12    id              INTEGER PRIMARY KEY AUTOINCREMENT,
13    property_id     BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
14    event           TEXT NOT NULL,
15    created_at      INTEGER NOT NULL,
16    user_id         TEXT,
17    url             TEXT,
18    title           TEXT,
19    referrer        TEXT,
20    user_agent      TEXT,
21    platform        TEXT,
22    browser         TEXT,
23    device          TEXT,
24    screen_width    INTEGER,
25    screen_height   INTEGER,
26    country         TEXT,
27    region          TEXT,
28    city            TEXT,
29    lat             REAL,
30    lon             REAL,
31    utm_source      TEXT,
32    utm_medium      TEXT,
33    utm_campaign    TEXT,
34    utm_term        TEXT,
35    utm_content     TEXT,
36    time_on_page_ms INTEGER,
37    extra           TEXT NOT NULL DEFAULT '{}'
38);
39CREATE INDEX events_property_created       ON events(property_id, created_at);
40CREATE INDEX events_property_event_created ON events(property_id, event, created_at);
41
42CREATE TABLE bot_events (
43    id           INTEGER PRIMARY KEY AUTOINCREMENT,
44    property_id  BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
45    event        TEXT NOT NULL,
46    created_at   INTEGER NOT NULL,
47    bot_name     TEXT,
48    url          TEXT,
49    user_agent   TEXT,
50    country      TEXT,
51    extra        TEXT NOT NULL DEFAULT '{}'
52);
53CREATE INDEX bot_events_property_created ON bot_events(property_id, created_at);
54
55-- meta key-value table for things like the Proprium property id
56CREATE TABLE meta (
57    key   TEXT PRIMARY KEY,
58    value TEXT NOT NULL
59);