KnowledgeTracker Platform Back to Requirements

Database Design

Multi-tenant PostgreSQL database schema designed for Supabase, ensuring complete data isolation, role-based access control, and scalable architecture.

Database Schema

organizations

Primary tenant entity representing each organization using the platform

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
name	VARCHAR(255)	NOT NULL, UNIQUE	Organization display name
slug	VARCHAR(100)	NOT NULL, UNIQUE	URL-friendly identifier
domain	VARCHAR(255)	UNIQUE	Custom domain for the organization
settings	JSONB	DEFAULT ''	Organization-specific settings (branding, features)
status	VARCHAR(20)	NOT NULL, DEFAULT 'active'	active, trial, suspended, archived
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT NOW()	Last update timestamp

Indexes:

• idx_organizations_slug ON (slug)
• idx_organizations_status ON (status)

users

User accounts scoped to organizations

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
organization_id	UUID	NOT NULL, FK → organizations	Organization this user belongs to
email	VARCHAR(255)	NOT NULL	User email address
password_hash	VARCHAR(255)	NOT NULL	Bcrypt hashed password
first_name	VARCHAR(100)	NOT NULL	User's first name
last_name	VARCHAR(100)	NOT NULL	User's last name
profile_data	JSONB	DEFAULT ''	Additional profile fields (avatar, bio, etc.)
status	VARCHAR(20)	NOT NULL, DEFAULT 'active'	active, inactive, suspended, pending
email_verified	BOOLEAN	DEFAULT false	Email verification status
last_login_at	TIMESTAMP		Last login timestamp
created_at	TIMESTAMP	DEFAULT NOW()	Account creation timestamp
updated_at	TIMESTAMP	DEFAULT NOW()	Last profile update timestamp

Constraints:

• UNIQUE (organization_id, email) - Email unique per organization
• ON DELETE CASCADE - Users deleted when organization is deleted

Indexes:

• idx_users_organization_id ON (organization_id)
• idx_users_email ON (email)
• idx_users_status ON (status)

roles

Organization-specific roles for access control

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
organization_id	UUID	NOT NULL, FK → organizations	Organization this role belongs to
name	VARCHAR(100)	NOT NULL	Role name (Admin, Instructor, etc.)
description	TEXT		Role description
is_system_role	BOOLEAN	DEFAULT false	System roles cannot be deleted
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT NOW()	Last update timestamp

Constraints:

• UNIQUE (organization_id, name) - Role name unique per organization
• ON DELETE CASCADE - Roles deleted when organization is deleted

Indexes:

• idx_roles_organization_id ON (organization_id)

permissions

Global permissions available across all organizations

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
name	VARCHAR(100)	NOT NULL, UNIQUE	Permission name (course.create, user.edit)
resource	VARCHAR(100)	NOT NULL	Resource type (course, user, assessment)
action	VARCHAR(50)	NOT NULL	Action type (view, create, edit, delete)
description	TEXT		Permission description
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp

role_permissions

Junction table linking roles to their assigned permissions

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
role_id	UUID	NOT NULL, FK → roles	Role receiving the permission
permission_id	UUID	NOT NULL, FK → permissions	Permission being assigned
created_at	TIMESTAMP	DEFAULT NOW()	Assignment timestamp

Constraints:

• UNIQUE (role_id, permission_id) - Prevent duplicate assignments
• ON DELETE CASCADE - Remove when role or permission is deleted

Indexes:

• idx_role_permissions_role_id ON (role_id)
• idx_role_permissions_permission_id ON (permission_id)

user_roles

Junction table tracking which users have which roles

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier
user_id	UUID	NOT NULL, FK → users	User receiving the role
role_id	UUID	NOT NULL, FK → roles	Role being assigned
assigned_at	TIMESTAMP	DEFAULT NOW()	Assignment timestamp
assigned_by	UUID	FK → users	User who made the assignment (audit trail)

Constraints:

• UNIQUE (user_id, role_id) - Prevent duplicate role assignments
• ON DELETE CASCADE - Remove when user or role is deleted

Indexes:

• idx_user_roles_user_id ON (user_id)
• idx_user_roles_role_id ON (role_id)

Multi-Tenant Data Isolation Strategy

Row-Level Isolation

All tenant-specific tables include organization_id as a foreign key. Every query MUST filter by organization_id to ensure complete data isolation.

-- Correct: Filtered by organization

SELECT * FROM users

WHERE organization_id = $1;

Foreign Key Cascades

ON DELETE CASCADE ensures that when an organization is deleted, all related data (users, roles, assignments) is automatically removed.

organization_id UUID NOT NULL

REFERENCES organizations(id)

ON DELETE CASCADE

Entity Relationship Diagram


┌─────────────────┐
│  organizations  │
│   (tenants)     │
└────────┬────────┘
         │
         │ 1:N
         ├──────────────────┬────────────────┐
         │                  │                │
         ▼                  ▼                ▼
    ┌────────┐         ┌────────┐    ┌───────────────┐
    │ users  │         │ roles  │    │  permissions  │
    └───┬────┘         └───┬────┘    │   (global)    │
        │                  │          └───────┬───────┘
        │                  │                  │
        │                  │ M:N              │
        │                  └──────────────────┤
        │                           │         │
        │                           ▼         ▼
        │                  ┌──────────────────────┐
        │                  │  role_permissions    │
        │                  └──────────────────────┘
        │
        │ M:N
        └──────────┐
                   │
                   ▼
           ┌──────────────┐
           │  user_roles  │
           └──────────────┘

Sample SQL Queries

Check User Permissions

SELECT DISTINCT p.name, p.resource, p.action
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = $1 
  AND u.organization_id = $2;

Get All Users in an Organization with Their Roles

SELECT 
  u.id, u.email, u.first_name, u.last_name,
  array_agg(r.name) as roles
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE u.organization_id = $1
GROUP BY u.id;

Assign Multiple Permissions to a Role

INSERT INTO role_permissions (role_id, permission_id)
SELECT $1, p.id
FROM permissions p
WHERE p.name IN ('course.view', 'course.create', 'assessment.view')
ON CONFLICT (role_id, permission_id) DO NOTHING;

Find Organizations with Most Users

SELECT 
  o.name,
  o.status,
  COUNT(u.id) as user_count
FROM organizations o
LEFT JOIN users u ON o.id = u.organization_id
GROUP BY o.id, o.name, o.status
ORDER BY user_count DESC
LIMIT 10;