Database Schema

Complete reference for the PostgreSQL database schema used in Qubital backend.

Entity Relationship Diagram

Tables Overview

Table	Purpose
`users`	Authenticated users from WorkOS
`organizations`	Organizations from WorkOS
`rooms`	LiveKit meeting rooms
`user_sessions`	User login sessions with refresh tokens
`user_organizations`	Many-to-many: users ↔ organizations
`user_integrations`	Many-to-many: users ↔ integrations
`integrations`	Third-party integrations (Google Calendar, etc.)
`valid_room_combinations`	Whitelist of valid room layouts
`subscription_link`	Pilot program invitation links

Detailed Table Descriptions

users

Stores authenticated users synced from WorkOS.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
workos_id (VARCHAR UK) - Unique WorkOS user identifier
invitation_link (VARCHAR) - Optional pilot invitation link
last_logged_org (VARCHAR) - Last organization user logged into
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

One-to-many with user_sessions (CASCADE delete)
Many-to-many with organizations via user_organizations (CASCADE delete)
Many-to-many with integrations via user_integrations (CASCADE delete)

organizations

Stores organizations synced from WorkOS.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
org_name (VARCHAR) - Organization name (NOT unique - WorkOS is source of truth)
invite_id (VARCHAR UK) - Unique invite link ID for member invitations
workos_org_id (VARCHAR UK) - Unique WorkOS organization identifier
initialized (BOOLEAN) - Whether organization setup is complete
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

One-to-many with rooms (CASCADE delete)
Many-to-many with users via user_organizations (CASCADE delete)

rooms

Stores LiveKit room metadata.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
name (VARCHAR) - Room name
description (TEXT) - Room description
room_id (UUID UK) - Unique LiveKit room identifier
layout, dimension, style (VARCHAR) - Room configuration (validated by valid_room_combinations)
org_id (BIGINT FK) - Foreign key to organizations.id
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

Many-to-one with organizations (CASCADE delete when org deleted)
Many-to-one with valid_room_combinations (SET NULL if combination deleted)

user_sessions

Stores user authentication sessions.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
user_id (BIGINT FK) - Foreign key to users.id
session_id (VARCHAR UK) - Unique session identifier
refresh_token (TEXT) - WorkOS refresh token (encrypted at rest)
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

Many-to-one with users (CASCADE delete when user deleted)

user_organizations

Junction table for many-to-many relationship between users and organizations.

Columns:

user_id (BIGINT PK/FK) - Composite primary key + foreign key to users.id
org_id (BIGINT PK/FK) - Composite primary key + foreign key to organizations.id
role (VARCHAR) - User role in organization (admin, moderator, member)
status (VARCHAR) - Membership status (active, suspended, pending)
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

Many-to-one with users (CASCADE delete)
Many-to-one with organizations (CASCADE delete)

integrations

Stores types of third-party integrations available.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
integration (VARCHAR UK) - Unique integration name (e.g., "google-calendar")
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

One-to-many with user_integrations

user_integrations

Junction table tracking which integrations users have enabled.

Columns:

user_id (BIGINT PK/FK) - Composite primary key + foreign key to users.id
integration_id (BIGINT PK/FK) - Composite primary key + foreign key to integrations.id
refresh_token (TEXT) - OAuth refresh token for the integration
is_enabled (BOOLEAN) - Whether integration is currently active
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

Many-to-one with users (CASCADE delete)
Many-to-one with integrations (CASCADE delete)

valid_room_combinations

Whitelist of valid room configuration combinations.

Columns:

layout (VARCHAR PK) - Part of composite primary key
dimension (VARCHAR PK) - Part of composite primary key
style (VARCHAR PK) - Part of composite primary key
created_at (TIMESTAMP) - Auto-managed timestamp

Relationships:

One-to-many with rooms (SET NULL if combination deleted)

subscription_link

Stores pilot program invitation links.

Columns:

id (BIGSERIAL PK) - Auto-incrementing primary key
special_link (VARCHAR UK) - Unique invitation link
enabled (BOOLEAN) - Whether link is currently active
consumed_by_workos_id (VARCHAR UK) - WorkOS ID of user who used the link (one-time use)
created_at, updated_at (TIMESTAMP) - Auto-managed timestamps

Relationships:

None (standalone table)

Key Relationships Summary

users (1) ──────< (N) user_sessions
  │
  │ (M)
  └─────< user_organizations >─────┐
                                    │ (N)
organizations (1) ──────< (N) rooms │
                                    │
users (M) ──────< user_integrations >───── integrations (N)

valid_room_combinations (1) ──────< (N) rooms

subscription_link (1) ────── (0..1) users

Legend:

(1) = One
(N) = Many
(M) = Many-to-Many
PK = Primary Key
FK = Foreign Key
UK = Unique Key

Foreign Key Behaviors

Relationship	Delete Behavior
`user_sessions.user_id` → `users.id`	CASCADE (delete sessions when user deleted)
`user_organizations.user_id` → `users.id`	CASCADE
`user_organizations.org_id` → `organizations.id`	CASCADE
`rooms.org_id` → `organizations.id`	CASCADE (delete rooms when org deleted)
`user_integrations.user_id` → `users.id`	CASCADE
`user_integrations.integration_id` → `integrations.id`	CASCADE
`rooms.(layout,dimension,style)` → `valid_room_combinations.(layout,dimension,style)`	SET NULL

Constraints

Table	Constraint Type	Columns	Description
`users`	UNIQUE	`workos_id`	Ensures unique WorkOS user identifiers
`organizations`	UNIQUE	`invite_id`	Ensures unique invitation IDs (when not null)
`organizations`	UNIQUE	`workos_org_id`	Ensures unique WorkOS org identifiers (when not null)
`rooms`	UNIQUE	`room_id`	Ensures unique LiveKit room identifiers
`user_sessions`	UNIQUE	`session_id`	Ensures unique session identifiers
`integrations`	UNIQUE	`integration`	Ensures unique integration names
`subscription_link`	UNIQUE	`special_link`	Ensures unique invitation links
`subscription_link`	UNIQUE	`consumed_by_workos_id`	One-time use per user (when not null)
`user_organizations`	PRIMARY KEY	`(user_id, org_id)`	Prevents duplicate user-org memberships
`user_integrations`	PRIMARY KEY	`(user_id, integration_id)`	Prevents duplicate user-integration links
`valid_room_combinations`	PRIMARY KEY	`(layout, dimension, style)`	Ensures unique room configuration combinations

Indexes

users

idx_users_workos_id (UNIQUE) on workos_id - Fast WorkOS ID lookups
idx_users_created_at on created_at DESC - Sorting by creation date
idx_users_invitation_link on invitation_link WHERE NOT NULL - Pilot invite lookups

organizations

idx_organizations_org_name on org_name - Organization name searches
idx_organizations_invite_id (UNIQUE) on invite_id WHERE NOT NULL - Invite link lookups
idx_organizations_workos_org_id (UNIQUE) on workos_org_id WHERE NOT NULL - WorkOS org lookups
idx_organizations_created_at on created_at DESC - Sorting by creation date
idx_organizations_initialized on initialized WHERE TRUE - Active org filtering

rooms

idx_rooms_name on name - Room name searches
idx_rooms_name_lower on LOWER(name) - Case-insensitive name searches
idx_rooms_room_code (UNIQUE) on room_id - LiveKit room ID lookups
idx_rooms_org_id on org_id WHERE NOT NULL - Filtering by organization
idx_rooms_layout on layout WHERE NOT NULL - Layout filtering
idx_rooms_dimension on dimension WHERE NOT NULL - Dimension filtering
idx_rooms_style on style WHERE NOT NULL - Style filtering
idx_rooms_combination on (layout, dimension, style) WHERE ALL NOT NULL - Composite configuration lookup

user_sessions

idx_user_sessions_user_id on user_id - User's sessions lookup
idx_user_sessions_session_id (UNIQUE) on session_id - Session ID lookup
idx_user_sessions_created_at on created_at DESC - Session age sorting
idx_user_sessions_updated_at on updated_at DESC - Last activity sorting

user_organizations

idx_user_organizations_user_id on user_id - User's organizations lookup
idx_user_organizations_org_id on org_id - Organization's members lookup
idx_user_organizations_role on role - Role filtering
idx_user_organizations_status on status - Status filtering
idx_user_organizations_role_status on (role, status) - Composite role+status filtering

integrations

idx_integrations_integration (UNIQUE) on integration - Integration name lookups
idx_integrations_created_at on created_at DESC - Sorting by creation date

user_integrations

idx_user_integrations_user_id on user_id - User's integrations lookup
idx_user_integrations_integration_id on integration_id - Integration's users lookup
idx_user_integrations_enabled on is_enabled WHERE TRUE - Active integrations filtering

valid_room_combinations

idx_valid_room_combinations_layout on layout - Layout filtering
idx_valid_room_combinations_dimension on dimension - Dimension filtering
idx_valid_room_combinations_style on style - Style filtering

subscription_link

idx_subscription_link_special_link (UNIQUE) on special_link - Link lookups
idx_subscription_link_created_at on created_at DESC - Sorting by creation date
idx_subscription_link_enabled on enabled WHERE TRUE - Active links filtering
idx_subscription_link_consumed_by (UNIQUE) on consumed_by_workos_id WHERE NOT NULL - User consumption tracking

Cardinality

Users ↔ Organizations: Many-to-Many (through user_organizations)

One user can be a member of multiple organizations
One organization can have multiple users
Junction table stores role and status for each membership

Users → Sessions: One-to-Many

One user can have multiple active sessions
Each session belongs to exactly one user

Users ↔ Integrations: Many-to-Many (through user_integrations)

One user can enable multiple integrations
One integration type can be enabled by multiple users
Junction table stores OAuth tokens and enabled status

Organizations → Rooms: One-to-Many

One organization can own multiple rooms
Each room belongs to at most one organization (nullable)

Valid Room Combinations → Rooms: One-to-Many

One valid combination can be used by multiple rooms
Each room references at most one valid combination (nullable)

Subscription Links → Users: One-to-One (optional)

One link can be consumed by at most one user
One user can consume multiple links (but each link is single-use)

Sample SQL Queries

Creating a User with Session (Transaction)

BEGIN;

-- Insert user
INSERT INTO users (workos_id, created_at, updated_at)
VALUES ('user_123', NOW(), NOW())
RETURNING id;

-- Insert session (use returned id)
INSERT INTO user_sessions (user_id, session_id, refresh_token, created_at, updated_at)
VALUES (1, 'session_abc', 'refresh_token_xyz', NOW(), NOW());

COMMIT;

Getting User's Organizations with Roles

SELECT
    o.id,
    o.org_name,
    o.workos_org_id,
    uo.role,
    uo.status,
    uo.created_at as joined_at
FROM users u
INNER JOIN user_organizations uo ON u.id = uo.user_id
INNER JOIN organizations o ON uo.org_id = o.id
WHERE u.workos_id = 'user_123'
  AND uo.status = 'active'
ORDER BY o.org_name;

Listing Rooms in an Organization

SELECT
    r.id,
    r.name,
    r.description,
    r.room_id,
    r.layout,
    r.dimension,
    r.style,
    r.created_at
FROM rooms r
INNER JOIN organizations o ON r.org_id = o.id
WHERE o.workos_org_id = 'org_456'
ORDER BY r.created_at DESC
LIMIT 20 OFFSET 0;

Searching Rooms by Name (Case-Insensitive)

SELECT
    r.id,
    r.name,
    r.description,
    r.room_id
FROM rooms r
INNER JOIN organizations o ON r.org_id = o.id
WHERE o.workos_org_id = 'org_456'
  AND r.name ILIKE '%conference%'
ORDER BY r.name;

Getting User's Active Integrations

SELECT
    i.id,
    i.integration,
    ui.is_enabled,
    ui.created_at as enabled_at
FROM users u
INNER JOIN user_integrations ui ON u.id = ui.user_id
INNER JOIN integrations i ON ui.integration_id = i.id
WHERE u.workos_id = 'user_123'
  AND ui.is_enabled = true;

Adding User to Organization with Role

INSERT INTO user_organizations (user_id, org_id, role, status, created_at, updated_at)
VALUES (
    (SELECT id FROM users WHERE workos_id = 'user_123'),
    (SELECT id FROM organizations WHERE workos_org_id = 'org_456'),
    'member',
    'active',
    NOW(),
    NOW()
)
ON CONFLICT (user_id, org_id) DO UPDATE
SET role = EXCLUDED.role, status = EXCLUDED.status, updated_at = NOW();

Deleting an Organization (Cascades to Rooms and Memberships)

-- This automatically:
-- - Deletes all rooms in the organization
-- - Deletes all user_organizations entries
DELETE FROM organizations WHERE workos_org_id = 'org_456';

Consuming a Subscription Link

UPDATE subscription_link
SET consumed_by_workos_id = 'user_123',
    enabled = false,
    updated_at = NOW()
WHERE special_link = 'pilot-invite-abc'
  AND enabled = true
  AND consumed_by_workos_id IS NULL;

Checking if Room Configuration is Valid

SELECT EXISTS (
    SELECT 1
    FROM valid_room_combinations
    WHERE layout = 'grid'
      AND dimension = 'medium'
      AND style = 'modern'
) as is_valid;

Getting Organization Member Count

SELECT
    o.id,
    o.org_name,
    COUNT(uo.user_id) as member_count
FROM organizations o
         LEFT JOIN user_organizations uo ON o.id = uo.org_id AND uo.status = 'active'
WHERE o.workos_org_id = 'org_456'
GROUP BY o.id, o.org_name;

Entity Relationship Diagram​

Tables Overview​

Detailed Table Descriptions​

users​

organizations​

rooms​

user_sessions​

user_organizations​

integrations​

user_integrations​

valid_room_combinations​

subscription_link​

Key Relationships Summary​

Foreign Key Behaviors​

Constraints​

Indexes​

users​

organizations​

rooms​

user_sessions​

user_organizations​

integrations​

user_integrations​

valid_room_combinations​

subscription_link​

Cardinality​

Sample SQL Queries​

Creating a User with Session (Transaction)​

Getting User's Organizations with Roles​

Listing Rooms in an Organization​

Searching Rooms by Name (Case-Insensitive)​

Getting User's Active Integrations​

Adding User to Organization with Role​

Deleting an Organization (Cascades to Rooms and Memberships)​

Consuming a Subscription Link​

Checking if Room Configuration is Valid​

Getting Organization Member Count​

Entity Relationship Diagram

Tables Overview

Detailed Table Descriptions

users

organizations

rooms

user_sessions

user_organizations

integrations

user_integrations

valid_room_combinations

subscription_link

Key Relationships Summary

Foreign Key Behaviors

Constraints

Indexes

users

organizations

rooms

user_sessions

user_organizations

integrations

user_integrations

valid_room_combinations

subscription_link

Cardinality

Sample SQL Queries

Creating a User with Session (Transaction)

Getting User's Organizations with Roles

Listing Rooms in an Organization

Searching Rooms by Name (Case-Insensitive)

Getting User's Active Integrations

Adding User to Organization with Role

Deleting an Organization (Cascades to Rooms and Memberships)

Consuming a Subscription Link

Checking if Room Configuration is Valid

Getting Organization Member Count