tenant_memberships
This table connects a platform user profile to a specific tenant. It is the main tenant-scoped identity record and determines whether a given user belongs to a particular customer organization.
Purpose
The purpose of this table is to represent a user’s membership in a tenant. It answers the question: “Does this user belong to this company in DirtView, and in what status?”
What this table does
- Connects a user profile to a tenant
- Tracks membership state such as invited, active, or suspended
- Supports internal users, tenant users, and external guests
- Provides the identity anchor for company-level and project-level RBAC
Why this table is defined
This table is defined because a user can exist at the platform level without automatically belonging to every tenant. It also supports the possibility that the same person may belong to more than one tenant.
Columns
| Column | Type | Required | Description | Example |
|---|---|---|---|---|
id |
uuid | Yes | Primary key for the membership row | membership_123 |
tenant_id |
uuid | Yes | Tenant this membership belongs to | tenant_001 |
user_id |
uuid | Yes | User profile/auth identity linked to the tenant | user_123 |
status |
text | Yes | Membership state such as invited, active, inactive, or suspended | active |
is_active |
boolean | Yes | Convenience flag for membership usability | true |
is_guest |
boolean | Yes | Whether this tenant membership is for an external guest style user | false |
invited_by |
uuid | No | User who sent the invite | user_999 |
invited_email |
text | No | Email used during invitation flow | james@acme.com |
joined_at |
timestamptz | No | When the user accepted or activated membership | 2026-04-05 10:00:00+00 |
last_active_at |
timestamptz | No | Last known activity timestamp | 2026-04-11 08:30:00+00 |
access_expiry |
timestamptz | No | Used especially for scoped external guest access | 2026-05-01 00:00:00+00 |
badge_label |
text | No | Optional label such as “DirtView Staff” | DirtView Staff |
metadata |
jsonb | Yes | Flexible membership metadata | {"source":"invite"} |
Relationships
- tenant_id → tenants.id
- user_id → profiles.user_id
- id is referenced by user_company_roles.membership_id
- id is referenced by project membership and guest access tables
How it is used
- Resolved after login to determine whether the user belongs to the tenant
- Used by RLS and backend authorization checks
- Used as the basis for assigning company-level roles
- Used in invite, activation, deactivation, suspension, and guest flows
Access and security
- This is a core authorization table and must be protected carefully
- Suspending a membership should integrate with token revocation/session invalidation
- Users should not be able to arbitrarily change their own membership state
- Unique constraint on
(tenant_id, user_id)is essential
Example scenarios
Scenario 1: Tenant invite
A new membership row is created with status invited.
Scenario 2: User activation
After account setup, the membership becomes active and
joined_at is set.
Scenario 3: External guest
A scoped guest is represented by a membership with
is_guest = true.
Notes and assumptions
- This table does not itself define role permissions
- Role assignment lives in tables like
user_company_roles - This is the tenant-scoped bridge between identity and authorization