Database Schema Reference

MovaLab uses PostgreSQL (via Supabase) with 48+ tables, complete RLS coverage, and sophisticated views for capacity planning.

48+

Tables

100+

RLS Policies

Functions

Views

Core Tables by Category

User Management

user_profilesRLS

Extended user profile linked to Supabase Auth

id, email, name, image, bio, skills, workload_sentiment, is_superadmin

user_rolesRLS

Junction table for user-role assignments

id, user_id, role_id, assigned_at, assigned_by

Roles & Permissions

rolesRLS

Role definitions with JSONB permissions

id, name, department_id, permissions (JSONB), reporting_role_id, hierarchy_level, is_system_role

role_hierarchy_auditRLS

Audit trail for role hierarchy changes

id, role_id, changed_by, action, old_reporting_role_id, new_reporting_role_id

Organizational Structure

departmentsRLS

Business units (Engineering, Sales, etc.)

id, name, description, created_at, updated_at

accountsRLS

Client/customer accounts

id, name, description, primary_contact_email, account_manager_id, service_tier, status

account_membersRLS

User-account assignments

id, user_id, account_id, created_at

Project Management

projectsRLS

Core project entity with timeline and effort tracking

id, name, account_id, status, priority, start_date, end_date, estimated_hours, actual_hours

project_assignmentsRLS

User assignments to projects

id, project_id, user_id, role_in_project, assigned_at, removed_at

project_updatesRLS

Journal-style project status updates

id, project_id, content, created_by, created_at

project_issuesRLS

Project blockers and problems

id, project_id, content, status, resolved_by, resolved_at

Task Management

tasksRLS

Individual work items within projects

id, name, project_id, status, priority, due_date, estimated_hours, assigned_to

task_dependencies

Task dependency relationships

id, task_id, depends_on_task_id, dependency_type

task_week_allocationsRLS

Planned task hours per week

id, task_id, week_start_date, allocated_hours, assigned_user_id

Time & Capacity

user_availabilityRLS

Weekly work capacity per user (default 40h)

id, user_id, week_start_date, available_hours, schedule_data (JSONB)

time_entriesRLS

Actual time logged on tasks

id, task_id, user_id, hours_logged, entry_date, clock_session_id

clock_sessionsRLS

Active clock-in/out sessions

id, user_id, clock_in_time, clock_out_time, is_active, is_auto_clock_out

Database Views

Pre-computed views for capacity planning and analytics dashboards.

weekly_capacity_summary

Aggregated weekly metrics per user for capacity planning.

-- Columns: user_id, week_start_date, available_hours,
--          allocated_hours, actual_hours, utilization_rate,
--          remaining_capacity

department_capacity_summary

Department-wide capacity rollup for manager oversight.

-- Columns: department_id, week_start_date, team_size,
--          total_available_hours, total_allocated_hours,
--          total_actual_hours, department_utilization_rate

project_capacity_summary

Project-level capacity allocation and tracking.

-- Columns: project_id, week_start_date, assigned_users,
--          allocated_hours, actual_hours, total_estimated_hours

Critical Database Functions

get_week_start_date(input_date DATE)

Returns Monday of the week for any date (ISO week standard). Used throughout the system for consistent week calculations.

user_has_permission(user_id UUID, permission TEXT)

SECURITY DEFINER function for RLS policies. Checks if user has a specific permission via their roles.

user_is_superadmin(user_id UUID)

SECURITY DEFINER function that checks if user is a superadmin via system role or is_superadmin flag.

auto_clock_out_stale_sessions()

Auto-closes clock sessions after 16 hours. Prevents runaway sessions from skewing time tracking data.

Migration Strategy

MovaLab uses Supabase migrations with a dual-track approach for local and cloud environments.

# Migration order (IMPORTANT):
1. 01_schema_base.sql    - Base table schemas
2. 02_functions_fixed.sql - Database functions (SECURITY DEFINER)
3. 03_views.sql          - Capacity views
4. 04_rls_policies_fixed.sql - RLS policies
5. 05_triggers.sql       - Auto-update triggers

# Apply locally
npm run docker:reset

# Apply to cloud
supabase link --project-ref <your-ref>
supabase db push

Local Docker

All migrations run on npm run docker:start. Full reset with npm run docker:reset.

Cloud Supabase

Link with supabase link, push with supabase db push. Always test locally first.

Row Level Security

Every table has RLS policies enforcing access control at the database level. This provides defense in depth.

-- Example: Projects table RLS policy
CREATE POLICY "Users can view accessible projects"
ON public.projects
FOR SELECT
TO authenticated
USING (
  user_is_superadmin(auth.uid())
  OR created_by = auth.uid()
  OR assigned_user_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM project_assignments pa
    WHERE pa.project_id = projects.id
    AND pa.user_id = auth.uid()
    AND pa.removed_at IS NULL
  )
  OR EXISTS (
    SELECT 1 FROM account_members am
    WHERE am.account_id = projects.account_id
    AND am.user_id = auth.uid()
  )
);

RLS policies use SECURITY DEFINER functions to avoid circular dependencies when checking permissions.

Key Entity Relationships

Entity Relationship Overview:

auth.users (Supabase Auth)
    ↓ (1:1)
user_profiles
    ↓ (1:many via user_roles)
roles ← departments (many:1)
    ↓ (permissions JSONB)
[40 permissions]

accounts
    ↓ (1:many)
projects
    ↓ (1:many)
tasks
    ↓ (many:many via task_assignments)
user_profiles

projects ← project_assignments → user_profiles
accounts ← account_members → user_profiles

time_entries → tasks → projects → accounts
clock_sessions → user_profiles
user_availability → user_profiles (weekly capacity)

Explore the Schema

View the full schema in Supabase Studio or explore migration files on GitHub.

Migration Files Permissions Reference