Product Analytics ER diagram and database schema

What this schema models

A complete product analytics database design, the kind of data model behind a tool similar to Mixpanel: 27 tables, 13 enums and 48 relationships covering workspaces, projects and API keys, anonymous devices and identified persons with an identity alias graph, person property history, sessions, a raw event log with an event and property registry, daily rollups, feature flags with targeting rules, experiments with variants and exposures, funnels, versioned cohorts, saved reports and dashboards. THE FOUR DESIGN RULES 1. Raw events are immutable. events is append-only, partitioned by month on occurred_at, with a client-generated id so a retried batch is deduplicated. Sessions, rollups, cohorts and report results are all derived, and can be rebuilt from it. 2. Identity resolves through aliases, never by rewriting events. Every event keeps the distinct_id it was sent with; identity_aliases maps each distinct id to a person. When an anonymous visitor logs in or two persons merge, only alias rows change, and the whole history follows. 3. What happened is separate from what it means. Events carry raw names and JSONB properties; event_definitions and property_definitions add display names, descriptions, types, PII flags and a verified or hidden status. Definitions are created automatically on first sight, so tracking is never blocked by governance, and renaming an event never touches a row of data. 4. Derived data is cached or versioned, never half-built. A cohort recompute writes membership version n+1 in full and then flips current_version in one update; persons.properties is a cache folded from person_property_changes; a report result is only ever a cache of its stored query. DETAILS WORTH STEALING - Daily rollups keep a HyperLogLog sketch beside the count, because distinct users cannot be added across days but sketches can be merged. - Feature flags bucket users by hash(key + salt + distinct id), so assignment is deterministic, needs no stored rows, and SDKs can evaluate flags locally. - Experiments log exposure when the variant is first seen, not when the user is bucketed, so people who never reached the changed screen do not dilute the result; the primary metric is fixed before launch. - First-touch attribution (referrer and UTM tags) lives on the device, because it happened before identity did. - Ingestion keys are public and write-only, secret keys read data, and both are stored only as hashes. - Person property history answers "what plan were they on when they did that?" WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: workspaces and access, identity and sessions, events and registry, flags and experiments, cohorts and funnels, and dashboards and reports. Every table carries a note. Written for Postgres: event and person properties, flag rules, cohort and funnel definitions and saved queries are jsonb (JSON in MySQL, NVARCHAR(MAX) in SQL Server), and the raw events table is meant to be partitioned by time. Useful for developers building in-house analytics or an analytics product, and for students who need a realistic product analytics ER diagram for a DBMS project. This is a schema design reference; at real event volumes the events table usually moves to a columnar store while the shape stays the same.

Tables (27)

Relationships (48)

• api_keys.created_by references users.id many to one
• api_keys.project_id references projects.id many to one
• cohort_memberships.cohort_id references cohorts.id many to one
• cohort_memberships.person_id references persons.id many to one
• cohorts.created_by references users.id many to one
• cohorts.project_id references projects.id many to one
• daily_event_rollups.project_id references projects.id many to one
• dashboard_tiles.dashboard_id references dashboards.id many to one
• dashboard_tiles.saved_report_id references saved_reports.id many to one
• dashboards.created_by references users.id many to one
• dashboards.project_id references projects.id many to one
• devices.person_id references persons.id many to one
• devices.project_id references projects.id many to one
• event_definitions.owner_user_id references users.id many to one
• event_definitions.project_id references projects.id many to one
• event_property_usage.event_definition_id references event_definitions.id many to one
• event_property_usage.property_definition_id references property_definitions.id many to one
• events.project_id references projects.id many to one
• experiment_exposures.experiment_id references experiments.id many to one
• experiment_exposures.variant_id references experiment_variants.id many to one
• experiment_variants.experiment_id references experiments.id many to one
• experiments.created_by references users.id many to one
• experiments.primary_funnel_id references funnels.id many to one
• experiments.project_id references projects.id many to one
• experiments.winning_variant_id references experiment_variants.id many to one
• feature_flags.created_by references users.id many to one
• feature_flags.id references experiments.feature_flag_id one to one
• feature_flags.project_id references projects.id many to one
• flag_rules.cohort_id references cohorts.id many to one
• flag_rules.feature_flag_id references feature_flags.id many to one
• funnel_steps.funnel_id references funnels.id many to one
• funnels.created_by references users.id many to one
• funnels.project_id references projects.id many to one
• identity_aliases.person_id references persons.id many to one
• identity_aliases.project_id references projects.id many to one
• person_property_changes.person_id references persons.id many to one
• persons.merged_into_id references persons.id many to one
• persons.project_id references projects.id many to one
• projects.workspace_id references workspaces.id many to one
• property_definitions.project_id references projects.id many to one
• saved_reports.cohort_id references cohorts.id many to one
• saved_reports.created_by references users.id many to one
• saved_reports.funnel_id references funnels.id many to one
• saved_reports.project_id references projects.id many to one
• sessions.device_id references devices.id many to one
• sessions.project_id references projects.id many to one
• workspace_members.user_id references users.id many to one
• workspace_members.workspace_id references workspaces.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Product Analytics database schema?

27 tables and 48 relationships, across 214 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.