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

> Explore Plausible Analytics PostgreSQL database schemas for users, sites, and memberships. Understand Ecto schemas for core account data management and optimize your analytics setup.

- Repository: [Plausible Analytics/analytics](https://github.com/plausible/analytics)
- Tags: internals
- Published: 2026-05-19

---

**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`](https://github.com/plausible/analytics/blob/main/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`](https://github.com/plausible/analytics/blob/main/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`](https://github.com/plausible/analytics/blob/main/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`](https://github.com/plausible/analytics/blob/main/lib/plausible/auth/user.ex)
- **`sites`** contains domain configuration, feature toggles, rate limits, and a `team_id` foreign key in [`lib/plausible/site.ex`](https://github.com/plausible/analytics/blob/main/lib/plausible/site.ex)
- **`team_memberships`** links users to teams with role-based access control and unique constraints in [`lib/plausible/teams/membership.ex`](https://github.com/plausible/analytics/blob/main/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.