PostgreSQL Database Schemas for Sites, Users, and Memberships in Plausible Analytics

Plausible Analytics stores core account data in three main PostgreSQL tables—users, sites, and team_memberships—defined by the Ecto schemas Plausible.Auth.User, Plausible.Site, and Plausible.Teams.Membership.

The plausible/analytics repository implements a multi-tenant architecture where teams own analytics sites and users gain access through role-based memberships. Understanding these PostgreSQL database schemas is essential for anyone self-hosting Plausible or contributing to its Elixir/Phoenix codebase.

The users Table (Authentication and Identity)

The users table persists account data and authentication credentials through the Plausible.Auth.User schema defined in lib/plausible/auth/user.ex.

Core Fields

• id – bigserial primary key
• email – varchar storing the unique user address
• password_hash – bytea containing the Bcrypt-hashed password
• name – varchar for the full display name
• last_seen – timestamp tracking last activity
• theme – PostgreSQL enum('system','light','dark') for UI preferences
• email_verified – boolean flag for verification status
• previous_email – varchar holding the former address during pending changes
• notes – text field for CRM annotations
• totp_enabled – boolean toggling TOTP 2FA
• totp_secret – encrypted bytea for token generation
• totp_token – temporary varchar for current tokens
• totp_last_used_at – timestamp of last authentication
• last_team_identifier – uuid referencing the last active team

Enterprise-Only Fields

The following columns are created only in the Enterprise Edition (EE) build:

• type – enum('standard','sso') authentication method
• sso_identity_id – varchar from the SSO provider
• last_sso_login – timestamp of the most recent SSO sign-in
• sso_integration_id – bigint foreign key to auth_sso_integrations
• sso_domain_id – bigint foreign key to auth_sso_domains

Key Associations

The schema defines several Ecto relationships:

• has_many :sessions → Plausible.Auth.UserSession
• has_many :team_memberships → Plausible.Teams.Membership
• has_many :api_keys → Plausible.Auth.ApiKey
• has_one :google_auth → Plausible.Site.GoogleAuth

The sites Table (Analytics Targets)

Defined in lib/plausible/site.ex, the sites table stores configuration for each tracked domain through the Plausible.Site schema.

Configuration Fields

• id – bigserial primary key
• domain – unique varchar hostname (e.g., example.com)
• timezone – IANA timezone string (e.g., Etc/UTC)
• public – boolean visibility flag for shared dashboards
• stats_start_date – date marking the first day of retained statistics
• native_stats_start_at – timestamp for precise native stats calculation
• allowed_event_props – text[] array whitelisting custom event properties
• conversions_enabled, props_enabled, funnels_enabled – boolean toggles for feature flags

Rate Limiting and Domain History

The table includes fields for API governance and domain changes:

• ingest_rate_limit_scale_seconds – integer time window for rate limiting
• ingest_rate_limit_threshold – integer max requests per window
• domain_changed_from – varchar previous hostname after renames
• domain_changed_at – timestamp of the rename operation

Team Ownership

• team_id – bigint foreign key referencing teams.id, establishing the multi-tenant ownership model

Site Associations

• has_many :guest_memberships → Plausible.Teams.GuestMembership
• has_many :goals → Plausible.Goal
• has_one :tracker_script_configuration → Plausible.Site.TrackerScriptConfiguration
• has_one :weekly_report / has_one :monthly_report → reporting configurations

The team_memberships Table (Access Control)

The junction table team_memberships implements the many-to-many relationship between users and teams via the Plausible.Teams.Membership schema in lib/plausible/teams/membership.ex.

Membership Fields

• id – bigserial primary key
• user_id – bigint foreign key to users.id
• team_id – bigint foreign key to teams.id
• role – enum('owner','admin','member') defining permission levels
• is_autocreated – boolean set to true for the initial membership when a user creates their first team
• inserted_at / updated_at – timestamp audit columns

Database Indices

PostgreSQL enforces data integrity through:

• Unique index on (user_id, team_id) preventing duplicate memberships
• Index on team_id for fast team member lookups
• Index on role for efficient permission-based queries

How the Schemas Relate

These three tables form the backbone of Plausible's permission model. The users table authenticates individuals, sites belongs to a team through the team_id foreign key, and team_memberships grants users access to those sites with granular roles. When a user creates a new site, Plausible auto-creates a team and an owner membership with is_autocreated set to true.

Migration files in priv/repo/migrations/ (such as those adding notes to users or creating segments) maintain this schema structure across deployments.

Summary

• users stores authentication credentials, 2FA settings, and SSO metadata (EE only) in lib/plausible/auth/user.ex
• sites contains domain configuration, feature toggles, rate limits, and a team_id foreign key in lib/plausible/site.ex
• team_memberships links users to teams with role-based access control and unique constraints in lib/plausible/teams/membership.ex
• All tables use bigserial primary keys and support the multi-tenant architecture where teams own sites and users access them through memberships

Frequently Asked Questions

What PostgreSQL data type does Plausible use for primary keys?

Plausible uses bigserial (auto-incrementing 64-bit integers) for the id columns in users, sites, and team_memberships tables. This provides sufficient range for high-volume analytics installations while maintaining compatibility with Ecto's default id generation.

How are user passwords stored in the database?

Passwords are hashed using Bcrypt and stored in the password_hash column as bytea data. The raw password is never persisted; only the cryptographic hash remains in the PostgreSQL users table.

What is the difference between owner, admin, and member roles in team_memberships?

The role enum in team_memberships defines three permission tiers: owner provides full administrative rights including deletion, admin allows management of sites and members, and member grants basic viewing and editing capabilities. The is_autocreated flag specifically marks the initial owner membership created upon team creation.

Are the SSO-related columns available in the open-source version?

No. The type, sso_identity_id, last_sso_login, sso_integration_id, and sso_domain_id columns are only created in the Enterprise Edition (EE) build of Plausible Analytics. The open-source version uses standard email and password authentication exclusively.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}