Database Schema¶

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

Entity Relationship Diagram¶

erDiagram
    users ||--o{ user_sessions : "has"
    users ||--o{ user_integrations : "has"
    users }o--o{ organizations : "member of"

    organizations ||--o{ rooms : "owns"

    integrations ||--o{ user_integrations : "used by"

    valid_room_combinations ||--o{ rooms : "validates"

    subscription_link ||--o| users : "consumed by"

    users {
        bigserial id PK
        varchar workos_id UK "NOT NULL"
        varchar invitation_link
        varchar last_logged_org
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    organizations {
        bigserial id PK
        varchar org_name "NOT NULL"
        varchar invite_id UK "NULLABLE"
        varchar workos_org_id UK "NULLABLE"
        boolean initialized "DEFAULT false"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    rooms {
        bigserial id PK
        varchar name "NOT NULL"
        text description "DEFAULT ''"
        uuid room_id UK "NOT NULL"
        varchar layout
        varchar dimension
        varchar style
        bigint org_id FK "NULLABLE"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    user_sessions {
        bigserial id PK
        bigint user_id FK "NOT NULL"
        varchar session_id UK "NOT NULL"
        text refresh_token "NOT NULL"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    user_organizations {
        bigint user_id PK "NOT NULL, FK"
        bigint org_id PK "NOT NULL, FK"
        varchar role "DEFAULT member"
        varchar status "DEFAULT active"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    integrations {
        bigserial id PK
        varchar integration UK "NOT NULL"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    user_integrations {
        bigint user_id PK "NOT NULL, FK"
        bigint integration_id PK "NOT NULL, FK"
        text refresh_token "NULLABLE"
        boolean is_enabled "DEFAULT false"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

    valid_room_combinations {
        varchar layout PK "NOT NULL"
        varchar dimension PK "NOT NULL"
        varchar style PK "NOT NULL"
        timestamp created_at "DEFAULT NOW()"
    }

    subscription_link {
        bigserial id PK
        varchar special_link UK "NOT NULL"
        boolean enabled "DEFAULT false"
        varchar consumed_by_workos_id UK "NULLABLE"
        timestamp created_at "DEFAULT NOW()"
        timestamp updated_at "DEFAULT NOW()"
    }

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;