ANALYTICS_DB
All tables and fields in the first-party analytics database.
ANALYTICS_DB is the default template's analytics database. Its initial schema is defined in schema/analytics-init.sql. This reference covers analytics sessions, events, and custom properties by table, including field types, nullability, defaults, and database constraints.
Time and boolean values
Time fields use Unix milliseconds. Boolean values are stored as SQLite INTEGER values, typically with 1 for yes and 0 for no.
analytics_session
Stores the visitor identifier, device information, first activity time, and the time the server first received data for an analytics session.
| Field | Type | Nullable | Default | Key | Description |
|---|---|---|---|---|---|
site_id | TEXT | No | — | PK (composite) | Site ID, corresponding to analytics.site.id in config/deploy.ts. |
session_id | TEXT | No | — | PK (composite) | Analytics session ID. |
distinct_id | TEXT | Yes | — | — | Visitor identifier provided by application code. |
browser | TEXT | Yes | — | — | Browser name. |
os | TEXT | Yes | — | — | Operating system name. |
device | TEXT | Yes | — | — | Device type. |
screen | TEXT | Yes | — | — | Screen dimensions. |
language | TEXT | Yes | — | — | Browser language. |
country | TEXT | Yes | — | — | Country or region code. |
region | TEXT | Yes | — | — | Region. |
city | TEXT | Yes | — | — | City. |
first_seen_at | INTEGER | No | — | — | First activity time, in Unix milliseconds. |
first_received_at | INTEGER | No | — | — | Time the server first received data for this session, in Unix milliseconds. |
analytics_event
Stores page views and custom events, along with referrer, device, geographic, and performance data.
| Field | Type | Nullable | Default | Key | Description |
|---|---|---|---|---|---|
event_id | TEXT | No | — | PK | Analytics event ID. |
site_id | TEXT | No | — | FK → analytics_session.(site_id, session_id) (composite foreign key) | Site ID, corresponding to analytics.site.id in config/deploy.ts. |
session_id | TEXT | No | — | FK → analytics_session.(site_id, session_id) (composite foreign key) | Analytics session ID. |
visit_id | TEXT | No | — | — | Current visit ID. |
event_type | INTEGER | No | — | — | Analytics event type: 1 for page views, 2 for custom events, and 5 for performance data. |
event_name | TEXT | Yes | — | — | Event name. |
url_path | TEXT | No | '' | — | Page URL path. |
url_query | TEXT | Yes | — | — | Page URL query string. |
page_title | TEXT | Yes | — | — | Page title. |
hostname | TEXT | No | — | — | Page hostname. |
referrer_path | TEXT | Yes | — | — | Referring page's path. |
referrer_query | TEXT | Yes | — | — | Referring page's query string. |
referrer_domain | TEXT | Yes | — | — | Referring page's domain. |
utm_source | TEXT | Yes | — | — | UTM source. |
utm_medium | TEXT | Yes | — | — | UTM medium. |
utm_campaign | TEXT | Yes | — | — | UTM campaign name. |
utm_content | TEXT | Yes | — | — | UTM content identifier. |
utm_term | TEXT | Yes | — | — | UTM keyword. |
gclid | TEXT | Yes | — | — | Google Ads click identifier. |
fbclid | TEXT | Yes | — | — | Facebook click identifier. |
msclkid | TEXT | Yes | — | — | Microsoft Ads click identifier. |
ttclid | TEXT | Yes | — | — | TikTok click identifier. |
li_fat_id | TEXT | Yes | — | — | LinkedIn Ads click identifier. |
twclid | TEXT | Yes | — | — | X Ads click identifier. |
browser | TEXT | Yes | — | — | Browser name. |
os | TEXT | Yes | — | — | Operating system name. |
device | TEXT | Yes | — | — | Device type. |
screen | TEXT | Yes | — | — | Screen dimensions. |
language | TEXT | Yes | — | — | Browser language. |
country | TEXT | Yes | — | — | Country or region code. |
region | TEXT | Yes | — | — | Region. |
city | TEXT | Yes | — | — | City. |
distinct_id | TEXT | Yes | — | — | Visitor identifier provided by application code. |
tag | TEXT | Yes | — | — | Event tag. |
lcp | REAL | Yes | — | — | Largest Contentful Paint, in milliseconds. |
inp | REAL | Yes | — | — | Interaction to Next Paint, in milliseconds. |
cls | REAL | Yes | — | — | Cumulative Layout Shift. |
fcp | REAL | Yes | — | — | First Contentful Paint, in milliseconds. |
ttfb | REAL | Yes | — | — | Time to First Byte, in milliseconds. |
duration | REAL | Yes | — | — | Event or page duration, in the unit defined by the collector. |
created_at | INTEGER | No | — | — | Creation time, in Unix milliseconds. |
received_at | INTEGER | No | — | — | Time the data was received, in Unix milliseconds. |
analytics_event_property
Stores custom event properties by key.
| Field | Type | Nullable | Default | Key | Description |
|---|---|---|---|---|---|
event_id | TEXT | No | — | PK (composite) FK → analytics_event.event_id (cascade delete) | Analytics event ID. |
data_key | TEXT | No | — | PK (composite) | Custom property or trait key. |
site_id | TEXT | No | — | — | Site ID, corresponding to analytics.site.id in config/deploy.ts. |
data_type | INTEGER | No | — | — | Property value type: 1 for strings, 2 for numbers, 3 for booleans, 4 for dates, and 5 for string arrays. |
string_value | TEXT | Yes | — | — | String value. |
number_value | REAL | Yes | — | — | Numeric value. |
date_value | INTEGER | Yes | — | — | Date value, in Unix milliseconds. |
created_at | INTEGER | No | — | — | Creation time, in Unix milliseconds. |
analytics_session_trait
Stores custom session-level traits by key.
| Field | Type | Nullable | Default | Key | Description |
|---|---|---|---|---|---|
site_id | TEXT | No | — | PK (composite) FK → analytics_session.(site_id, session_id) (cascade delete) | Site ID, corresponding to analytics.site.id in config/deploy.ts. |
session_id | TEXT | No | — | PK (composite) FK → analytics_session.(site_id, session_id) (cascade delete) | Analytics session ID. |
data_key | TEXT | No | — | PK (composite) | Custom property or trait key. |
data_type | INTEGER | No | — | — | Trait value type: 1 for strings, 2 for numbers, 3 for booleans, 4 for dates, and 5 for string arrays. |
string_value | TEXT | Yes | — | — | String value. |
number_value | REAL | Yes | — | — | Numeric value. |
date_value | INTEGER | Yes | — | — | Date value, in Unix milliseconds. |
distinct_id | TEXT | Yes | — | — | Visitor identifier provided by application code. |
updated_at | INTEGER | No | — | — | Last update time, in Unix milliseconds. |