Portable Data

One file. Your data. Any tool.

Getting the File

The database lives at the path set by ECHELON_DB_PATH (default: ./echelon.db). In Docker deployments, it's at /app/data/echelon.db inside the container, mounted from the host's data/ directory.

Download a Copy

# From the Docker host — copy the live database
cp data/echelon.db ~/echelon-backup.db

# From a remote server
scp user@server:~/ea-private/data/echelon.db .

# Safe copy while the app is running (SQLite WAL checkpoint)
sqlite3 data/echelon.db "PRAGMA wal_checkpoint(TRUNCATE);"
cp data/echelon.db ~/echelon-snapshot.db

SQLite in WAL mode is safe to copy while the application is running. For a fully consistent snapshot, run the checkpoint command first to flush the write-ahead log into the main database file.

Open It Anywhere

# Command-line
sqlite3 echelon.db

# Python
import sqlite3
conn = sqlite3.connect("echelon.db")

# R
library(RSQLite)
con <- dbConnect(SQLite(), "echelon.db")

# DuckDB (for analytics queries)
duckdb -c "ATTACH 'echelon.db' AS ea (TYPE sqlite)"

# Any GUI: DB Browser for SQLite, TablePlus, DBeaver, DataGrip...

"Did you know DuckDB can attach your SQLite database directly and run analytics queries significantly faster? Great for large datasets — no ETL step needed." -🦆❤️🦭

Database Design

The schema is intentionally simple. Nine tables, no joins required for basic queries, no surrogate keys you need to look up. Every timestamp is ISO 8601 UTC. Every ID is human-readable text.

Design Principles

• Raw data first. Individual pageviews and events are stored as-is. Nothing is aggregated or discarded on write (unless bot score exceeds the discard threshold).
• Rollups for speed. Daily aggregates are pre-computed for fast dashboard queries, but you can always go back to the raw data.
• Bot scores preserved. Every record carries its bot score (0–100) so you can filter or re-analyze with your own thresholds.
• UTC everywhere. All created_at timestamps use strftime('%Y-%m-%dT%H:%M:%fZ','now') — ISO 8601, UTC, millisecond precision.
• Text IDs. Visitor IDs, session IDs, experiment IDs are all human-readable text. No opaque integer foreign keys to chase.

Table Reference

visitor_views

Every pageview recorded by the tracker. This is the primary analytics table — one row per page load.

Indexes: visitor_id, (site_id, created_at), path, country_code, device_type, (created_at, bot_score), utm_campaign, (utm_campaign, site_id, created_at WHERE utm_campaign IS NOT NULL).

visitor_views_daily

Pre-computed daily aggregates. Excludes bot traffic (score ≥50) and manually excluded visitors. Used by the dashboard for fast date-range queries.

Primary key: (site_id, date, device_type, country_code, is_returning). This composite key makes rollups idempotent — re-running the aggregation for the same day safely overwrites.

semantic_events

Behavioral and custom events: clicks, scroll depth, bounces, form submissions, A/B experiment conversions, and any events sent via the JavaScript API.

Indexes: (site_id, event_type, created_at), visitor_id, (created_at, bot_score), (experiment_id, variant_id), (utm_campaign, site_id WHERE utm_campaign IS NOT NULL).

excluded_visitors

Visitor IDs manually flagged for exclusion from rollups and dashboards.

experiments

A/B experiment definitions. Each experiment targets a specific event type as its success metric.

experiment_variants

Variants within an experiment. Each has a weight for traffic allocation and an optional JSON config payload.

Primary key: (experiment_id, variant_id). Foreign key cascades deletes from experiments.

utm_campaigns

Registered marketing campaigns for the campaign dashboard.

Unique constraint: (utm_campaign, site_id) — one campaign per site per UTM value.

site_settings

Per-site configuration, currently used for custom consent banner CSS.

perf_metrics

Performance benchmarks recorded by CI/CD or manual runs.

maintenance_log

Tracks daily rollup and purge operations.

Example Queries

Copy-paste these into sqlite3, DBeaver, a Python notebook, or any SQL tool.

Pageviews per day (human traffic only)

SELECT date(created_at) AS day, COUNT(*) AS views
FROM visitor_views
WHERE bot_score < 50
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Top pages this week

SELECT path, COUNT(*) AS views, COUNT(DISTINCT visitor_id) AS visitors
FROM visitor_views
WHERE bot_score < 50
  AND created_at >= date('now', '-7 days')
GROUP BY path
ORDER BY views DESC
LIMIT 20;

Unique visitors by country

SELECT country_code, COUNT(DISTINCT visitor_id) AS visitors
FROM visitor_views
WHERE bot_score < 50
  AND created_at >= date('now', '-30 days')
GROUP BY country_code
ORDER BY visitors DESC;

Device breakdown

SELECT device_type,
       COUNT(*) AS views,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM visitor_views
WHERE bot_score < 50
  AND created_at >= date('now', '-30 days')
GROUP BY device_type
ORDER BY views DESC;

Average interaction time by page

SELECT path,
       COUNT(*) AS views,
       ROUND(AVG(interaction_ms) / 1000.0, 1) AS avg_seconds
FROM visitor_views
WHERE bot_score < 50
  AND interaction_ms > 0
  AND created_at >= date('now', '-30 days')
GROUP BY path
ORDER BY avg_seconds DESC
LIMIT 20;

Event type distribution

SELECT event_type, COUNT(*) AS count
FROM semantic_events
WHERE bot_score < 50
  AND created_at >= date('now', '-30 days')
GROUP BY event_type
ORDER BY count DESC;

Returning vs new visitors

SELECT
  CASE is_returning WHEN 1 THEN 'returning' ELSE 'new' END AS type,
  COUNT(DISTINCT visitor_id) AS visitors,
  COUNT(*) AS pageviews
FROM visitor_views
WHERE bot_score < 50
  AND created_at >= date('now', '-30 days')
GROUP BY is_returning;

UTM campaign performance

SELECT utm_campaign,
       COUNT(*) AS pageviews,
       COUNT(DISTINCT visitor_id) AS visitors,
       ROUND(AVG(interaction_ms) / 1000.0, 1) AS avg_seconds
FROM visitor_views
WHERE utm_campaign IS NOT NULL
  AND bot_score < 50
GROUP BY utm_campaign
ORDER BY pageviews DESC;

Bot traffic analysis

SELECT
  CASE
    WHEN bot_score >= 50 THEN 'likely bot'
    WHEN bot_score >= 25 THEN 'suspect'
    ELSE 'likely human'
  END AS category,
  COUNT(*) AS views,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM visitor_views
WHERE created_at >= date('now', '-7 days')
GROUP BY category
ORDER BY views DESC;

Experiment conversion rates

SELECT e.name AS experiment,
       se.variant_id,
       COUNT(*) AS conversions,
       COUNT(DISTINCT se.visitor_id) AS unique_converters
FROM semantic_events se
JOIN experiments e ON e.experiment_id = se.experiment_id
WHERE se.event_type = e.metric_event_type
GROUP BY e.name, se.variant_id
ORDER BY e.name, se.variant_id;

Hourly traffic pattern

SELECT
  CAST(strftime('%H', created_at) AS INTEGER) AS hour_utc,
  COUNT(*) AS views
FROM visitor_views
WHERE bot_score < 50
  AND created_at >= date('now', '-7 days')
GROUP BY hour_utc
ORDER BY hour_utc;

Data Lifecycle

SQLite Pragmas

Echelon configures these pragmas on startup:

Tips

• Always filter on bot_score < 50 for human analytics. Omit it only when you want to analyze bot traffic.
• Use the rollup table (visitor_views_daily) for dashboards and long-range trends — it's pre-filtered and indexed for fast date-range queries.
• The data column in semantic_events is JSON. Use SQLite's json_extract() to query inside it: json_extract(data, '$.amount').
• Timestamps are strings in ISO 8601 format. SQLite's date(), time(), and strftime() functions work directly on them.
• Back up regularly. It's just a file: cp, rsync, or your favorite backup tool.
• DuckDB can attach SQLite databases directly and run analytical queries significantly faster than SQLite for large datasets.
• AI agents can query your database directly using the MCP server — copy the file to any machine, point ECHELON_DB_PATH at it, and ask questions in natural language.