logo
ExamplesEditorPricingDocs
37.1kSign in

Product analytics platform database schema

How a product analytics service stores its data: projects and a discovered event registry, raw events with JSONB properties, anonymous and identified users with identity merges, sessions, funnels, cohorts, retention reports and precomputed metric snapshots.

Analytics Platform ER diagram

Product analytics platform database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 projects idBIGINT slugVARCHAR nameVARCHAR write_keyVARCHAR reporting_timezoneVARCHAR raw_event_retenti…INTEGER person_retention_…INTEGER is_activeBOOLEAN created_atTIMESTAMPTZ event_definitions idBIGINT project_idBIGINT nameVARCHAR display_nameVARCHAR categoryVARCHAR property_schemaJSONB is_hiddenBOOLEAN first_seen_atTIMESTAMPTZ last_seen_atTIMESTAMPTZ events idBIGINT project_idBIGINT event_definition_…BIGINT person_idBIGINT anonymous_idVARCHAR session_idBIGINT insert_idVARCHAR propertiesJSONB contextJSONB occurred_atTIMESTAMPTZ received_atTIMESTAMPTZ persons idBIGINT project_idBIGINT canonical_distinc…VARCHAR is_identifiedBOOLEAN traitsJSONB merged_into_perso…BIGINT first_seen_atTIMESTAMPTZ last_seen_atTIMESTAMPTZ person_identifiers idBIGINT project_idBIGINT person_idBIGINT identifier_typeVARCHAR valueVARCHAR first_seen_atTIMESTAMPTZ last_seen_atTIMESTAMPTZ identity_merges idBIGINT project_idBIGINT source_person_idBIGINT target_person_idBIGINT triggerVARCHAR identifier_typeVARCHAR identifier_valueVARCHAR events_reassignedBIGINT is_reversedBOOLEAN merged_atTIMESTAMPTZ sessions idBIGINT project_idBIGINT person_idBIGINT anonymous_idVARCHAR started_atTIMESTAMPTZ ended_atTIMESTAMPTZ duration_secondsINTEGER event_countINTEGER entry_pathVARCHAR exit_pathVARCHAR referrer_domainVARCHAR device_typeVARCHAR funnels idBIGINT project_idBIGINT nameVARCHAR conversion_window…INTEGER counting_methodVARCHAR breakdown_propertyVARCHAR created_atTIMESTAMPTZ updated_atTIMESTAMPTZ funnel_steps idBIGINT funnel_idBIGINT step_indexSMALLINT event_definition_…BIGINT labelVARCHAR filterJSONB cohorts idBIGINT project_idBIGINT nameVARCHAR definitionJSONB is_staticBOOLEAN member_countINTEGER refresh_interval_…INTEGER last_refreshed_atTIMESTAMPTZ created_atTIMESTAMPTZ cohort_members cohort_idBIGINT person_idBIGINT added_atTIMESTAMPTZ removed_atTIMESTAMPTZ retention_reports idBIGINT project_idBIGINT nameVARCHAR start_event_defin…BIGINT return_event_defi…BIGINT cohort_idBIGINT window_unitVARCHAR window_countSMALLINT is_rollingBOOLEAN created_atTIMESTAMPTZ metric_snapshots idBIGINT project_idBIGINT metric_keyVARCHAR grainVARCHAR bucket_startTIMESTAMPTZ dimension_keyVARCHAR dimension_valueVARCHAR valueNUMERIC computed_atTIMESTAMPTZ

Related database schemas

PostgreSQL
SaaS

A/B testing & experimentation platform schema

An experimentation platform end to end: experiments and weighted variants, deterministic hash bucketing, exposure events, metric definitions, per-variant aggregates with sufficient statistics, significance results and staged rollout.

PostgreSQL
SaaS

Email marketing & campaign database schema

How a campaign sender stores its data: lists and contacts with custom fields, rule-based segments, campaigns with A/B variants, one send row per recipient, opens and clicks, bounces and suppressions, and drip automations.

PostgreSQL
SaaS

Help desk & customer support ticketing schema

A support desk modelled properly: tickets in queues, one thread that mixes public replies with internal notes, SLA clocks measured in business hours with breach tracking, macros, tags, merges and CSAT.

PostgreSQL
SaaS

Multi-tenant SaaS database schema

Shared-database multi-tenancy done properly: organizations as tenants, global users joined through memberships, invitations, scoped API keys, plans, subscriptions, metered usage and an append-only audit log.