Single-binary self-hosted website analytics on Rust axum: collector API, dashboards, world map, and PDF reports.
analyticsaxumdockerrustself-hostedsqliteviteweb-analytics
1//! One-shot migration from the original Django analytics SQLite into the
2//! Rust hot-field schema. Preserves property UUIDs so embedded snippets on
3//! tracked sites keep working without a snippet rotation.
4//!
5//! Invoked as a subcommand of the main binary so it ships in the existing
6//! Docker image with no extra wiring:
7//!
8//! ```text
9//! ./analytics migrate <path-to-django.sqlite3> [--force]
10//! ```
11//!
12//! Without `--force`, refuses to run if the destination has any properties,
13//! events, or bot_events. With `--force`, wipes those tables (and `meta`)
14//! before importing — so an auto-created Proprium row from a prior boot is
15//! replaced with the original Proprium from Django.
16
17use anyhow::{bail, Context, Result};
18use sqlx::{Acquire, Row};
19use std::path::{Path, PathBuf};
20use uuid::Uuid;
21
22pub async fn run(source: PathBuf, force: bool) -> Result<()> {
23 if !source.exists() {
24 bail!("source database not found: {}", source.display());
25 }
26
27 let data_dir = std::env::var("ANALYTICS_DATA_DIR")
28 .map(PathBuf::from)
29 .unwrap_or_else(|_| PathBuf::from("./data"));
30 std::fs::create_dir_all(&data_dir)?;
31
32 let pool = crate::db::init(&data_dir).await?;
33
34 let dest_props: i64 =
35 sqlx::query_scalar("SELECT COUNT(*) FROM properties").fetch_one(&pool).await?;
36 let dest_events: i64 =
37 sqlx::query_scalar("SELECT COUNT(*) FROM events").fetch_one(&pool).await?;
38 let dest_bots: i64 =
39 sqlx::query_scalar("SELECT COUNT(*) FROM bot_events").fetch_one(&pool).await?;
40
41 if dest_props + dest_events + dest_bots > 0 {
42 if !force {
43 bail!(
44 "destination not empty (properties={dest_props}, events={dest_events}, bot_events={dest_bots}); pass --force to wipe before migrating"
45 );
46 }
47 eprintln!(
48 "wiping destination: {dest_props} properties, {dest_events} events, {dest_bots} bot_events"
49 );
50 sqlx::query("DELETE FROM events").execute(&pool).await?;
51 sqlx::query("DELETE FROM bot_events").execute(&pool).await?;
52 sqlx::query("DELETE FROM properties").execute(&pool).await?;
53 sqlx::query("DELETE FROM meta").execute(&pool).await?;
54 }
55
56 // ATTACH source DB. SQLite won't attach across pool connections cleanly,
57 // so grab a single connection and use it for the whole migration.
58 let mut conn = pool.acquire().await?;
59 let attach_sql = format!("ATTACH DATABASE '{}' AS src", escape_path(&source));
60 sqlx::query(&attach_sql).execute(&mut *conn).await?;
61
62 // 1. Read django properties (host-side parse so we can convert hex UUIDs
63 // to 16-byte BLOBs in Rust without depending on a specific SQLite
64 // version's unhex() availability).
65 let prop_rows = sqlx::query(
66 "SELECT id, name, custom_cards, is_protected, is_public, created_at, updated_at \
67 FROM src.properties_property",
68 )
69 .fetch_all(&mut *conn)
70 .await
71 .context("reading source properties")?;
72
73 if prop_rows.is_empty() {
74 bail!("source database has no properties; nothing to migrate");
75 }
76
77 eprintln!("found {} properties in source", prop_rows.len());
78
79 let mut tx = conn.begin().await?;
80
81 let mut proprium_blob: Option<Vec<u8>> = None;
82 for row in &prop_rows {
83 let id_text: String = row.try_get("id")?;
84 let name: String = row.try_get("name")?;
85 let custom_cards: Option<String> = row.try_get("custom_cards")?;
86 let is_protected: i64 = row.try_get("is_protected")?;
87 let is_public: i64 = row.try_get("is_public")?;
88 let created_at: String = row.try_get("created_at")?;
89 let updated_at: String = row.try_get("updated_at")?;
90
91 let uuid = parse_django_uuid(&id_text)
92 .with_context(|| format!("parsing property id {id_text:?}"))?;
93 let id_blob = uuid.as_bytes().to_vec();
94
95 sqlx::query(
96 "INSERT INTO properties (id, name, custom_cards, is_protected, is_public, created_at, updated_at) \
97 VALUES (?, ?, ?, ?, ?, \
98 CAST((julianday(?) - 2440587.5) * 86400000 AS INTEGER), \
99 CAST((julianday(?) - 2440587.5) * 86400000 AS INTEGER))",
100 )
101 .bind(&id_blob)
102 .bind(&name)
103 .bind(custom_cards.unwrap_or_else(|| "[]".to_string()))
104 .bind(is_protected)
105 .bind(is_public)
106 .bind(&created_at)
107 .bind(&updated_at)
108 .execute(&mut *tx)
109 .await
110 .with_context(|| format!("inserting property {name:?}"))?;
111
112 if name == "Proprium" && is_protected != 0 {
113 proprium_blob = Some(id_blob);
114 }
115 }
116
117 // 2. Build a temp mapping (text-hex id → BLOB) so the events INSERT…SELECT
118 // can join across the ATTACHed database without per-row Rust roundtrips.
119 sqlx::query("CREATE TEMP TABLE prop_id_map (text_id TEXT PRIMARY KEY, blob_id BLOB NOT NULL)")
120 .execute(&mut *tx)
121 .await?;
122 for row in &prop_rows {
123 let id_text: String = row.try_get("id")?;
124 let uuid = parse_django_uuid(&id_text)?;
125 sqlx::query("INSERT INTO prop_id_map (text_id, blob_id) VALUES (?, ?)")
126 .bind(&id_text)
127 .bind(uuid.as_bytes().to_vec())
128 .execute(&mut *tx)
129 .await?;
130 }
131
132 // 3. Bot events first — rows where data.is_bot is set route to bot_events
133 // with a smaller projection.
134 let bot_count = sqlx::query(
135 "INSERT INTO bot_events (property_id, event, created_at, bot_name, url, user_agent, country, extra) \
136 SELECT \
137 m.blob_id, \
138 e.event, \
139 CAST((julianday(e.created_at) - 2440587.5) * 86400000 AS INTEGER), \
140 json_extract(e.data, '$.bot_name'), \
141 json_extract(e.data, '$.url'), \
142 json_extract(e.data, '$.user_agent'), \
143 json_extract(e.data, '$.country'), \
144 '{}' \
145 FROM src.properties_event e \
146 JOIN prop_id_map m ON e.property_id = m.text_id \
147 WHERE json_extract(e.data, '$.is_bot') IS NOT NULL",
148 )
149 .execute(&mut *tx)
150 .await
151 .context("inserting bot_events")?
152 .rows_affected();
153
154 // 4. Human events. Project every hot field via json_extract; lat/lon
155 // come out of Django's `loc: [lat, lon]` array; `time_on_page` (ms)
156 // flows into `time_on_page_ms`. The `extra` blob stays empty since
157 // Django stored everything in `data` and the hot fields cover what
158 // the dashboard uses.
159 let human_count = sqlx::query(
160 "INSERT INTO events ( \
161 property_id, event, created_at, user_id, url, title, referrer, user_agent, \
162 platform, browser, device, screen_width, screen_height, country, region, city, \
163 lat, lon, utm_source, utm_medium, utm_campaign, utm_term, utm_content, \
164 time_on_page_ms, extra \
165 ) \
166 SELECT \
167 m.blob_id, \
168 e.event, \
169 CAST((julianday(e.created_at) - 2440587.5) * 86400000 AS INTEGER), \
170 CAST(json_extract(e.data, '$.user_id') AS TEXT), \
171 json_extract(e.data, '$.url'), \
172 json_extract(e.data, '$.title'), \
173 json_extract(e.data, '$.referrer'), \
174 json_extract(e.data, '$.user_agent'), \
175 json_extract(e.data, '$.platform'), \
176 json_extract(e.data, '$.browser'), \
177 json_extract(e.data, '$.device'), \
178 json_extract(e.data, '$.screen_width'), \
179 json_extract(e.data, '$.screen_height'), \
180 json_extract(e.data, '$.country'), \
181 json_extract(e.data, '$.region'), \
182 json_extract(e.data, '$.city'), \
183 json_extract(e.data, '$.loc[0]'), \
184 json_extract(e.data, '$.loc[1]'), \
185 json_extract(e.data, '$.utm_source'), \
186 json_extract(e.data, '$.utm_medium'), \
187 json_extract(e.data, '$.utm_campaign'), \
188 json_extract(e.data, '$.utm_term'), \
189 json_extract(e.data, '$.utm_content'), \
190 json_extract(e.data, '$.time_on_page'), \
191 '{}' \
192 FROM src.properties_event e \
193 JOIN prop_id_map m ON e.property_id = m.text_id \
194 WHERE json_extract(e.data, '$.is_bot') IS NULL",
195 )
196 .execute(&mut *tx)
197 .await
198 .context("inserting events")?
199 .rows_affected();
200
201 // 5. Persist the Proprium id so self-tracking continues without a fresh row.
202 if let Some(blob) = proprium_blob {
203 let uuid = Uuid::from_slice(&blob)?;
204 sqlx::query("INSERT OR REPLACE INTO meta (key, value) VALUES ('proprium_id', ?)")
205 .bind(uuid.to_string())
206 .execute(&mut *tx)
207 .await?;
208 eprintln!("set proprium_id = {uuid}");
209 } else {
210 eprintln!("no Proprium property found in source — server will create a new one on next boot");
211 }
212
213 sqlx::query("DROP TABLE prop_id_map").execute(&mut *tx).await?;
214 tx.commit().await?;
215
216 sqlx::query("DETACH DATABASE src").execute(&mut *conn).await?;
217
218 eprintln!(
219 "migrated {} properties, {human_count} events, {bot_count} bot_events",
220 prop_rows.len()
221 );
222 Ok(())
223}
224
225/// Parse a Django-stored UUID (32 lowercase hex chars, no dashes) into a `Uuid`.
226fn parse_django_uuid(s: &str) -> Result<Uuid> {
227 Uuid::parse_str(s).context("expected 32-hex Django UUID")
228}
229
230/// Quote a path for use in an `ATTACH DATABASE 'path' AS src` statement.
231/// SQLite uses single-quote-doubled escaping inside string literals.
232fn escape_path(p: &Path) -> String {
233 p.display().to_string().replace('\'', "''")
234}